HELP! MORTGAGE SCHEDULE WITH EXTRA PMTS EVERY 4 MONTHS

[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

 

[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.

 

[ryguy7272]

Extra Payment Mortgage Calculator
http://www.vertex42.com/ExcelTemplates/extra-payments.html

Other good samples here:
http://www.vertex42.com/ExcelTemplates/mortgage-interest-spreadsheet.html

Regards,
Ryan---

 

[Jagaude]

Extra Payment Mortgage Calculatorhttp://www.vertex42.com/ExcelTemplates/extra-payments.html

Other good samples here:http://www.vertex42.com/ExcelTemplates/mortgage-interest-spreadsheet....

Regards,
Ryan---
--
RyGuy








- Show quoted text -

Thanks guys. I used the MOD fonction and it works good.

Cheers,

Jagaude