[edbyrd]

Can someone help with with an Excel formula for a mortgage constant with the following rules for each cell: Loan Amount W77 ($100,000), Interest rate W78 (5.125%), Amortization W81 (240 months).

[CastleHeart]

Interest rate W78 (5.125%)
Amortization W81 (240 months)
Loan Amount W77 ($100,000)

the MC is:

MC=(W78/100/12)/(1-(1/(POWER((1+(W78/100/12)),W81))))

MC * $100,000 = $666.80 per month (for your example) So let's say this formula is in W80....

W80*W77 is the payment

The interest (5.125%) was divided by 100 and the 12 months
The maturity (240) is in months

- Castleheart




hope I did that right. try some examples and see...

[AbvAvgUser]

I have a whole lot of Per Lakh (100,000) tables with me. That will make calculation of Rate of Interest from EMI (Equalted Monthly Installment) very very easy. You can use them the other way round as well. All you need is just a calculator or Pen/Paper.

If you need amortisation statements, I have those too. Give me your email address in a private message and I will mail them all to you.

[AbvAvgUser]

BTW, just forgot to mention. They are all in Excel 2000 format. Some have been converted in HTML format but they exist in excel also

[CastleHeart]

FYI:

Looking back at my post I realized that the formula I submitted just looks like a bunch of numbers to most readers, so for those who are interested in knowing it, the basic formula is:

MC=i/{1-[1/(1+i)ª]}


Where:
MC is the mortgage constant
i is the interest
a is the period in months
( I used "a" in this post because I didn't know how to display "m" here as an exponent!)

You then multiply MC times the loan amount for a monthly payment.

- C

[AbvAvgUser]

Will have a look at your formula and will also test it for its accuracy. Thanks for it.

[analyst1]

What is that formula factored out to solve for i? I have the loan constant and the amortization and need to know the interest rate. Thanks!