Mortgage amortization schedule with Interval Extra Payments

[Jagaude]

Can anyone help me with this challenge. I have an amortization
schedule calculating mortgages. Everything works fine except that I
would like to add a feature for interval extra payments. Does anyone
know how to do this?



Based on a date, insert pmt in a cell depending on the interval that
was decided.



Ex:



No. Due Date Payment Due Principal Additional Payment Interest
Balance

1 03/07/2008 1,270.51 218.76 1,270.51 1,051.75 249,083.73
2 04/07/2008 1,270.51 225.01 1,270.51 1,045.50 247,588.21
3 05/07/2008 1,270.51 231.29 1,270.51 1,039.22 246,086.41
4 06/07/2008 1,270.51 237.59 1,270.51 1,032.92 244,578.31
5 07/07/2008 1,270.51 243.92 1,270.51 1,026.59 243,063.88
6 08/07/2008 1,270.51 250.28 1,270.51 1,020.23 241,543.09
7 09/07/2008 1,270.51 256.66 1,270.51 1,013.85 240,015.92
8 10/07/2008 1,270.51 263.07 1,270.51 1,007.44 238,482.34




I would like to add an additional payment only every 2 months and not
every months.



Cheers,



Jagaude

 

[joeu2004]

Based on a date, insert pmt in a cell depending on the interval that
was decided.
[....]
I would like to add an additional payment only every 2 months and
not every months.

Those are two very different requirements, the first being more
general than the second.

If all you want to do is make an additional payment every 2 months,
and if the additional payment is the same as the monthly payment (as
in your example), the following might meet your needs.

In the Additional Payment column (starting in E2 and copy down):

=if(E1="", C2, 0)



----- original posting -----

 

[Jagaude]

Based on a date, insert pmt in a cell depending on theintervalthat
was decided.
[....]
I would like to add an additionalpaymentonly every 2 months and
not every months.

Those are two very different requirements, the first being more
general than the second.

If all you want to do is make an additionalpaymentevery 2 months,
and if the additionalpaymentis the same as the monthlypayment(as
in your example), the following might meet your needs.

In the AdditionalPaymentcolumn (starting in E2 and copy down):

=if(E1="", C2, 0)

----- original posting -----

Can anyone help me with this challenge. I have anamortization
schedule calculating mortgages. Everything works fine except that I
would like to add a feature forintervalextra payments. Does anyone
know how to do this?

Based on a date, insert pmt in a cell depending on theintervalthat
was decided.

No. Due DatePaymentDue Principal   AdditionalPaymentInterest
Balance

1 03/07/2008 1,270.51  218.76   1,270.51  1,051.75  249,083.73
2 04/07/2008 1,270.51  225.01   1,270.51  1,045.50  247,588.21
3 05/07/2008 1,270.51  231.29   1,270.51  1,039.22  246,086.41
4 06/07/2008 1,270.51  237.59   1,270.51  1,032.92  244,578.31
5 07/07/2008 1,270.51  243.92   1,270.51  1,026.59  243,063.88
6 08/07/2008 1,270.51  250.28   1,270.51  1,020.23  241,543.09
7 09/07/2008 1,270.51  256.66   1,270.51  1,013.85  240,015.92
8 10/07/2008 1,270.51  263.07   1,270.51  1,007.44  238,482.34

I would like to add an additionalpaymentonly every 2 months and not
every months.

Jagaude- Hide quoted text -

- Show quoted text -

The problem is that the amount of extra pmt is not necessarely the
same as the regular monthly payments. If I have a mortgage, the pmts
are set but if I want to calculate the impact of adding an extra 200$
every 3 months, how do I come up with the formula?

 

[joeu2004]

The problem is that the amount of extra pmt is not necessarely the
same as the regular monthly payments. If I have a mortgage, the pmts
are set but if I want to calculate the impact of adding an extra 200$
every 3 months, how do I come up with the formula?

You keep changing the requirements: Now it's every 3 months instead
of every 2 months. Do you really want a solution for "every N
months", where N is variable (i.e. a cell value)?

That's hard. But for "every 3 months", one solution might be: Put
the following formula into E3 and copy down:

=if(and(n(E1)=0,n(E2)=0), 200, 0)

Of course, you could put 200 into a cell and replace "200" in IF()
with an absolute reference to the cell, for example $Z$1.

NOTE: I had an error in the formula in my first response. The use of
N() allows cells in column E to contain text (including "") or a
number or to be empty (no formula or constant). The IF() results in
zero instead of "" because "" might adversely affect your formula to
compute the balance, unless you were careful.