HELP! MORTGAGE SCHEDULE WITH EXTRA PMTS EVERY 4 MONTHS


J

Jagaude

Inputs Summary
Loan Amount $250,573.00
Annual Interest rate 5.09% Lump sum $50.00
Term of loan in years 34.83 Starting 1 03/07/2008
Payment Frequency Monthly Ending 12 02/07/2009
Closing date 03/07/2008 Interval 0

Payment (per period) $1,270.51

Amortization Schedule
No. Due Date Payment Due Principal Additional Payment
Interest Balance
$250,573.00
1 03/07/2008 1,270.51 218.76 50.00 1,051.75 250,304.24
2 04/07/2008 1,270.51 219.89 50.00 1,050.62 250,034.35
3 05/07/2008 1,270.51 221.02 50.00 1,049.49 249,763.33
4 06/07/2008 1,270.51 222.16 50.00 1,048.35 249,491.17
5 07/07/2008 1,270.51 223.30 50.00 1,047.21 249,217.87
6 08/07/2008 1,270.51 224.45 50.00 1,046.06 248,943.42
7 09/07/2008 1,270.51 225.60 50.00 1,044.91 248,667.82
8 10/07/2008 1,270.51 226.76 50.00 1,043.75 248,391.06
9 11/07/2008 1,270.51 227.92 50.00 1,042.59 248,113.14
10 12/07/2008 1,270.51 229.09 50.00 1,041.42 247,834.05
11 01/07/2009 1,270.51 230.26 50.00 1,040.25 247,553.79
12 02/07/2009 1,270.51 231.43 50.00 1,039.08 247,272.36
13 03/07/2009 1,270.51 232.62 0.00 1,037.89 247,039.74
14 04/07/2009 1,270.51 233.59 0.00 1,036.92 246,806.15

WHAT FORMULA SHOULD I PUT IN MY ADDITIONAL PAYMENT SO THAT I CAN PLAY
WITH DIFFERENT INTERVALS. FOR EXAMPLE, AN EXTRA PMT EVERY 4 MONTHS
STARTING ON THE 6 MONTH AND ENDING ON THE 24 MONTHS.

THANKS,

JAGAUDE
 
Ad

Advertisements

F

Fred Smith

My first suggestion is to forget about additional payments, and modify the
regular payment. So instead of trying to calculate the effect of an extra
$150 every three months, use an extra $50 every month. This will get you a
result that is close enough for almost every circumstance.

If you insist on irregular addtional payments, my next recommendation is to
enter them manually. In your example, you want 'an extra amt every 4th month
starting on the 6th month and ending on the 24th month.' So just enter those
numbers in your table. It won't take you more than a minute.

If you want to make data entry a little easier, enter your payment pattern
(eg: 100, 0, 0, 0). Now highlight those four cells, and drag them down as
far as you want. Excel will fill in that pattern for all cells you drag to.

If you really want a programmatic solution, you need to use the Mod
function. So for every 4th month, you want mod(month,4)=0. So create an If
statement like:
=if(mod(month(f2),4)=0,$ExtraPayment,0)
and copy it down your column. This will only work if 12 can be divided by
your interval (eg, 2, 3, 4, 6 and 12 month intervals).

Regards,
Fred.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top