Mortgage amortization schedule with Interval Extra Payments

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jagaude - 17 Mar 2008 19:28 GMT

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

Reply to this Message

joeu2004 - 17 Mar 2008 20:41 GMT

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

> Can anyone help me with this challenge. I have an amortization
> schedule calculating mortgages. Everything works fine except that I
[quoted text clipped - 24 lines]
>
> Jagaude

Reply to this Message

Jagaude - 18 Mar 2008 16:22 GMT

> > Based on a date, insert pmt in a cell depending on theintervalthat
> > was decided.
[quoted text clipped - 45 lines]
>
> - 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?

Reply to this Message

joeu2004 - 19 Mar 2008 00:59 GMT

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

Reply to this Message