Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Mortgage Loan Constant Formula


(!)

edbyrd's Avatar
edbyrd edbyrd is offline
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: Orlando, FL
17-Mar-2004, 07: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's Avatar
CastleHeart CastleHeart is offline
Senior Member with 743 posts.
 
Join Date: May 2002
Location: Coast of North Carolina
18-Mar-2004, 10: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...
AbvAvgUser's Avatar
Senior Member with 2,281 posts.
 
Join Date: Oct 2002
Location: Mumbai, India
Experience: Intermediate
20-Mar-2004, 12: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.
AbvAvgUser's Avatar
Senior Member with 2,281 posts.
 
Join Date: Oct 2002
Location: Mumbai, India
Experience: Intermediate
20-Mar-2004, 12: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's Avatar
CastleHeart CastleHeart is offline
Senior Member with 743 posts.
 
Join Date: May 2002
Location: Coast of North Carolina
22-Mar-2004, 10: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
AbvAvgUser's Avatar
Senior Member with 2,281 posts.
 
Join Date: Oct 2002
Location: Mumbai, India
Experience: Intermediate
23-Mar-2004, 12:57 AM #6
Will have a look at your formula and will also test it for its accuracy. Thanks for it.
analyst1's Avatar
analyst1 analyst1 is offline
Junior Member with 1 posts.
 
Join Date: Sep 2008
Experience: Intermediate
15-Sep-2008, 07: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!
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
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)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑