# Mortgage Loan Constant Formula

Discussion in 'Business Applications' started by edbyrd, Mar 17, 2004.

Not open for further replies.

Joined:
Jul 12, 2003
Messages:
19
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).

3. ### CastleHeart

Joined:
May 4, 2002
Messages:
743
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...

4. ### AbvAvgUser

Joined:
Oct 3, 2002
Messages:
2,281
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.

5. ### AbvAvgUser

Joined:
Oct 3, 2002
Messages:
2,281
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

6. ### CastleHeart

Joined:
May 4, 2002
Messages:
743
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

7. ### AbvAvgUser

Joined:
Oct 3, 2002
Messages:
2,281
Will have a look at your formula and will also test it for its accuracy. Thanks for it.

8. ### analyst1

Joined:
Sep 15, 2008
Messages:
1
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!

As Seen On