Search Search for: Business ApplicationsAll Forums

# Mortgage Loan Constant Formula

 edbyrd
Junior Member with 19 posts.

Join Date: Jul 2003
Location: Orlando, FL
17-Mar-2004, 08:47 PM #1
Mortgage Loan Constant Formula
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
Senior Member with 743 posts.

Join Date: May 2002
Location: Coast of North Carolina
18-Mar-2004, 11:00 AM #2
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...
Senior Member with 2,281 posts.

Join Date: Oct 2002
Location: Mumbai, India
Experience: Intermediate
20-Mar-2004, 01:49 AM #3
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.
Senior Member with 2,281 posts.

Join Date: Oct 2002
Location: Mumbai, India
Experience: Intermediate
20-Mar-2004, 01:50 AM #4
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
Senior Member with 743 posts.

Join Date: May 2002
Location: Coast of North Carolina
22-Mar-2004, 11:04 AM #5
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
Senior Member with 2,281 posts.

Join Date: Oct 2002
Location: Mumbai, India
Experience: Intermediate
23-Mar-2004, 01:57 AM #6
Will have a look at your formula and will also test it for its accuracy. Thanks for it.
 analyst1
Junior Member with 1 posts.

Join Date: Sep 2008
Experience: Intermediate
15-Sep-2008, 08:02 PM #7
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!
 techguy.org/212705
As Seen On

WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

### Find the solution to your computer problem!

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)