software

Hello World,
The information I am looking for is a math expression.
If you would like to offer some additional explanations and comments and so
on, that would be fine too, but remember, all I'm looking for is a math
expression.
I need a math expression, that can be used in Excel, to calculate the
principal portion of a monthly payment, in a specific month, after a large
one-time extra payment is applied the previous month.
Let's look at an example.
Question:

What is the mathematical calculation (in Excel) that results in the
number $418.15.
Conditions:

Loan amount is $260,000

Rate is 5.625%

Loan term is 360 months

One extra payment of $25,000 is made at month number 17.

State is California.
When I use a webpage calculator I get this amortization:

The amount of monthly payment applied to principal in month 16 is 298.16.

The amount of monthly payment applied to principal in month 17 is 299.56.

An extra payment towards principal is made in month 17 of $25,000.

The amount of monthly payment applied to principal in month 18 is 418.15
(instead of 300.96).
As an example of the type of nomenclature I am looking for, the Excel
calculation that results in month 17 $299.56 is:

PPMT(5.625%, 17, 360, 260000)

equals 299.56 (negated).
My problem is that I cannot formulate an expression that accurately results
in the month 18 principal of 418.15.
I predicted the quot;right answerquot; using this web page:

www.decisionaide.com/mpcalcul...aPayments1.asp
According to the web page, this reduces the months by 70, from 360 to 290. I
don't know how to calculation the 290 either, so if you can help me with
that too that would be great.
Thanks World!=-PMT(0.05625/12,360,260000)-0.05625/12*(260000*(1-(1-(1 0.05625/12)^17)/(1-(1 0.05625/12)^360))-25000)

DaveFor the number of months,
=NPER(0.05625/12,PMT(0.05625/12,360,260000),(260000*(1-(1-(1 0.05625/12)^17)/(1-(1 0.05625/12)^360))-25000)) 17

Davegt; What is the mathematical calculation (in Excel) that results in the
gt; number $418.15.

Hi. Just for a general discussion:

Your monthly payment is fixed at:
=PMT(5.625%/12,360,-260000)
or $1,496.71

After 17 months, you have paid off the loan by:
=CUMPRINC(5.625%/12,360,260000,1,17,0)
or -4906.68

Your new balance is now:
=260000 -4906.68 -25000
or
230,093.32

The interest you should pay on the next payment in month 18 is just
230,093.32 * 5.625%/12
or 1,078.56

What's left over on your payment goes towards principle:
1496.71 - 1078.56

or 418.15

--
HTH. :gt;)
Dana DeLouis
Windows XP, Office 2003quot;Gary Wachsquot; gt; wrote in message
...
gt;
gt;
gt; Hello World,
gt;
gt;
gt;
gt; The information I am looking for is a math expression.
gt;
gt;
gt;
gt; If you would like to offer some additional explanations and comments and
gt; so on, that would be fine too, but remember, all I'm looking for is a math
gt; expression.
gt;
gt;
gt;
gt; I need a math expression, that can be used in Excel, to calculate the
gt; principal portion of a monthly payment, in a specific month, after a large
gt; one-time extra payment is applied the previous month.
gt;
gt;
gt;
gt; Let's look at an example.
gt;
gt;
gt;
gt; Question:
gt;
gt; What is the mathematical calculation (in Excel) that results in the
gt; number $418.15.
gt;
gt;
gt;
gt; Conditions:
gt;
gt; Loan amount is $260,000
gt;
gt; Rate is 5.625%
gt;
gt; Loan term is 360 months
gt;
gt; One extra payment of $25,000 is made at month number 17.
gt;
gt; State is California.
gt;
gt;
gt;
gt; When I use a webpage calculator I get this amortization:
gt;
gt; The amount of monthly payment applied to principal in month 16 is 298.16.
gt;
gt; The amount of monthly payment applied to principal in month 17 is 299.56.
gt;
gt; An extra payment towards principal is made in month 17 of $25,000.
gt;
gt; The amount of monthly payment applied to principal in month 18 is 418.15
gt; (instead of 300.96).
gt;
gt;
gt;
gt; As an example of the type of nomenclature I am looking for, the Excel
gt; calculation that results in month 17 $299.56 is:
gt;
gt; PPMT(5.625%, 17, 360, 260000)
gt;
gt; equals 299.56 (negated).
gt;
gt;
gt;
gt; My problem is that I cannot formulate an expression that accurately
gt; results in the month 18 principal of 418.15.
gt;
gt;
gt;
gt; I predicted the quot;right answerquot; using this web page:
gt;
gt;
gt; www.decisionaide.com/mpcalcul...aPayments1.asp
gt;
gt;
gt;
gt; According to the web page, this reduces the months by 70, from 360 to 290.
gt; I don't know how to calculation the 290 either, so if you can help me with
gt; that too that would be great.
gt;
gt;
gt;
gt; Thanks World!
gt;
gt; =-PMT(0.05625/12,360,260000)-0.05625/12*(260000*(1-(1-(1 0.05625/12)^17)/(1-(1 0.05625/12)^360))-25000)

Perhaps we could merge Excel's PMT function into your excellent equation
from above.

= r * (((r 1) ^ n * s) / ((r 1) ^ 360 - 1) xp)

Here's the vba version if the op wishes to follow...

Sub Demo()
'// Dana DeLouis
Dim r, n, s, xp

r = 0.05625 / 12
s = 260000
xp = 25000
n = 17

Debug.Print _
r * (((r 1) ^ n * s) / ((r 1) ^ 360 - 1) xp)

' 418.144224788268
End Sub

--
HTH. :gt;)
Dana DeLouis
Windows XP, Office 2003quot;Dave Dodsonquot; gt; wrote in message ups.com...
gt; =-PMT(0.05625/12,360,260000)-0.05625/12*(260000*(1-(1-(1 0.05625/12)^17)/(1-(1 0.05625/12)^360))-25000)
gt;
gt; Dave
gt;

Ok good - outstanding, that's exactly what I needed.

In your opinion is it a good idea to make this contribution.
Having crunched the numbers it certaintly looks worthwhile. It makes PMI go
away, it saves $80k and it shortens the loan by 6 yrs.
Historic Samp;P growth of about 12% annually, if it continues, would outperform
the above savings. Maybe I should put the money into the market instead of
the principal.

--
quot;Dana DeLouisquot; gt; wrote in message
...
gt;gt; What is the mathematical calculation (in Excel) that results in the
gt;gt; number $418.15.
gt;
gt; Hi. Just for a general discussion:
gt;
gt; Your monthly payment is fixed at:
gt; =PMT(5.625%/12,360,-260000)
gt; or $1,496.71
gt;
gt; After 17 months, you have paid off the loan by:
gt; =CUMPRINC(5.625%/12,360,260000,1,17,0)
gt; or -4906.68
gt;
gt; Your new balance is now:
gt; =260000 -4906.68 -25000
gt; or
gt; 230,093.32
gt;
gt; The interest you should pay on the next payment in month 18 is just
gt; 230,093.32 * 5.625%/12
gt; or 1,078.56
gt;
gt; What's left over on your payment goes towards principle:
gt; 1496.71 - 1078.56
gt;
gt; or 418.15
gt;
gt; --
gt; HTH. :gt;)
gt; Dana DeLouis
gt; Windows XP, Office 2003
On Sun, 16 Apr 2006 01:15:07 GMT, quot;Gary Wachsquot; gt; wrote:

gt;
gt;Ok good - outstanding, that's exactly what I needed.
gt;
gt;In your opinion is it a good idea to make this contribution.
gt;Having crunched the numbers it certaintly looks worthwhile. It makes PMI go
gt;away, it saves $80k and it shortens the loan by 6 yrs.
gt;Historic Samp;P growth of about 12% annually, if it continues, would outperform
gt;the above savings. Maybe I should put the money into the market instead of
gt;the principal.

You have a few things to consider with your extra payment.

1. Repayment
What happens is that you make the extra payment when your $1 is worth $1 but the
return on that investment doesn't come back to you until the end of the loan
period when the $1 is worth say $0.80. In your case if the $1 was worth $0.80
at the end of the loan period and you invested $25,000 you would need to save
paying in excess of $31,250 due to shortening the loan period to make it
worthwhile.

2. Investing
If you invested the $25,000 for the period of the loan you would also have to
consider how much tax you would need to pay on the investment.

What you then need to do is to deduct the tax from the interest earned each year
for the period of the loan and do a present worth analysis over the shortened
period of the loan on the difference each year to take into account that the $1
in say 10 years is worth say $0.80 and compare this with the amount returned by
the repayment in 1. above to finally have your answer.
--

Cheers . . . JC