Loan Amortization Formula
 

I have a problem that is more of a math problem than excel.
I want to show an annual loan interest calculation that is based on a
monthly amortization schedule.
The problem is that each month we repay some of the principal and then next
months interest is calculated on a different principal amount.
Illustrated I am imagining that this is how this would look assuming we
start with a balance of $1,000 and an monthly interest rate of r.

1,000*r+(1,000-((1,000*r))*r+(1,000-(1,000-((1,000*r))*r)...and so on

This seems like some sort of math series and I am wondering if this can be
simplified in a formula.

If anyone can help I would really appreciate it.
Thanks.

Neda

Loan Amortization Formula
 

I want to show an annual loan interest calculation that is based on a
monthly amortization schedule.

Hi. I had a hard time with this one. To check one's work, here is a loan
template with extra payments.

http://office.microsoft.com/en-us/te...?AxInstalled=1

For a closed form equation, I assume you are making a constant "Extra
Payment."
If r is your monthly interest rate,
p is your mortgage payment plus a constant "Extra Payment",
n is a particular month, then the interest at that particular month is given
by:

(Loan*r-p)*(r+1)^(n-1)+p

If you would like to sum a 12 month period, and call that period a year
(y=1, 2, or 3...etc)
then perhaps this equation. Sorry, but I couldn't find a smaller equation.
:(

((r + 2)*r*(r + 1)^2 + 1)*(r^2 + r + 1)*
((r + 2)*r + 2)*(r + 2)*((r + 3)*r + 3)*
(Loan*r - p)*(r + 1)^(12*(y - 1)) + 12*p;

The above equations seemed to check with sample data on the above loan
sheet.
You will have to make an adjustment near the end of the loan since the loan
balance gets to zero faster.
--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"neda5" wrote in message
...
I have a problem that is more of a math problem than excel.
I want to show an annual loan interest calculation that is based on a
monthly amortization schedule.
The problem is that each month we repay some of the principal and then
next
months interest is calculated on a different principal amount.
Illustrated I am imagining that this is how this would look assuming we
start with a balance of $1,000 and an monthly interest rate of r.

1,000*r+(1,000-((1,000*r))*r+(1,000-(1,000-((1,000*r))*r)...and so on

This seems like some sort of math series and I am wondering if this can be
simplified in a formula.

If anyone can help I would really appreciate it.
Thanks.

Neda

Loan Amortization Formula
 

Thanks I will try the equations.
Neda

"Dana DeLouis" wrote:

I want to show an annual loan interest calculation that is based on a
monthly amortization schedule.

Hi. I had a hard time with this one. To check one's work, here is a loan
template with extra payments.

http://office.microsoft.com/en-us/te...?AxInstalled=1

For a closed form equation, I assume you are making a constant "Extra
Payment."
If r is your monthly interest rate,
p is your mortgage payment plus a constant "Extra Payment",
n is a particular month, then the interest at that particular month is given
by:

(Loan*r-p)*(r+1)^(n-1)+p

If you would like to sum a 12 month period, and call that period a year
(y=1, 2, or 3...etc)
then perhaps this equation. Sorry, but I couldn't find a smaller equation.
:(

((r + 2)*r*(r + 1)^2 + 1)*(r^2 + r + 1)*
((r + 2)*r + 2)*(r + 2)*((r + 3)*r + 3)*
(Loan*r - p)*(r + 1)^(12*(y - 1)) + 12*p;

The above equations seemed to check with sample data on the above loan
sheet.
You will have to make an adjustment near the end of the loan since the loan
balance gets to zero faster.
--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"neda5" wrote in message
...
I have a problem that is more of a math problem than excel.
I want to show an annual loan interest calculation that is based on a
monthly amortization schedule.
The problem is that each month we repay some of the principal and then
next
months interest is calculated on a different principal amount.
Illustrated I am imagining that this is how this would look assuming we
start with a balance of $1,000 and an monthly interest rate of r.

1,000*r+(1,000-((1,000*r))*r+(1,000-(1,000-((1,000*r))*r)...and so on

This seems like some sort of math series and I am wondering if this can be
simplified in a formula.

If anyone can help I would really appreciate it.
Thanks.

Neda