Results 1 to 8 of 8

20011213, 14:34 #1
 Join Date
 Mar 2001
 Posts
 191
 Thanks
 0
 Thanked 1 Time in 1 Post
Calculating Mortague Principle Due (Excel 97)
I am trying to figure out how to determine how much of the principal of a mortague is still due after so many years. Can't seem to find the formula. As an example if the loan was $100,000 at 8% how much of the principal is stil due to be paid after 8 years paying on the loan, the 9th year, etc. I would very much appreciate any help...All I can find is how to determine the amount of payment with is of no help....thanks in advance....John

20011213, 16:31 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Calculating Mortague Principle Due (Excel 97)
If you install the Analysis Toolpak you can use =CUMPRIN() to derive the Principal paid, from which it's easy to derive Principal remaining. Here's a sample calculation, assuming the top left hand cell is A1 (if you paste this directly to XL, some cells may be misaligned), with some of the XL97 Help text:
Initial Principal 100000
Rate 0.08
Periods =30*12 'John ... I float in liquid gardens
UTC 7ąDS

20011213, 17:37 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Mortague Principle Due (Excel 97)
I think you will find that the function is written for real life mortgage/loans. What you say is true of all loans/mortgages, and all of Excel's function are written to account for that.
Legare Coleman

20011213, 17:38 #4
 Join Date
 Apr 2001
 Location
 New York, New York, USA
 Posts
 2,328
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Calculating Mortague Principle Due (Excel 97)
NOT IN THE REAL LIFE...
The function you mention  CUMPRIN()  deals with loans, not reallife mortgages. The problem with mortgages is: payments at the beginning are made against interest (usually 90%95% of each payment amount) rather then principal ( 5%  10% of the payment). After several years, depending of the agreement, this ratio changes, and at the end almost 100% of each payment will be made against principal. If you need to calculate particular reallife mortgage, contact your financial institution.

20011213, 17:50 #5
 Join Date
 Apr 2001
 Location
 New York, New York, USA
 Posts
 2,328
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Calculating Mortague Principle Due (Excel 97)
You are right  thank you

20011213, 18:03 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Calculating Mortague Principle Due (Excel 97)
I understand what you are saying, but in my review of the function, I believe that the function correctly represents the application of payments to principal over time. Look at the attached spreadsheet where I have added some analysis of the percentage of payments going to principal and the rate of principal repayment (albeit on an annual average basis, but if you wanted to you could run it for all 360 months or other appropriate period), and decide for yourself.
John ... I float in liquid gardens
UTC 7ąDS

20011214, 13:19 #7
 Join Date
 Mar 2001
 Posts
 191
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Calculating Mortague Principle Due (Excel 97)
I would like to say a big thank you to JohnBF and everyone else for the assistance....not only did I learn something, but was able to find the function and use it to my advantage....by running out examples of my existing mortgage and what the rates are now...I could determine whether it was worth refinancing and when the additional amounts of principal would be added in my account......Tlhanks again, Happy Holidays to all.....JohnL

20011221, 17:14 #8
 Join Date
 Dec 2000
 Location
 Los Angeles Area, California, USA
 Posts
 7,453
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Mortague Principle Due (Excel 97)
Hi kaplinb:
Just to clarify something. Loan payments are not based on ratios at all. The fact is that you borrow a fixed amount (say, $100,000 for example). A nominal rate of 12%/year is 1%/month. (Compounded monthly is more than 12%/year, but you can do calculations monthly anyhow)
The agreement doesn't really change in the typical mortgage (variable rate mortgages are different). The mortgage is divided into uniform monthly payments. 1% of the entire loan in the first month is 1% of $100,000, or $1,000. By the time you've paid the loan down to, say $10,000, the interest is only $10. That's why there's a difference in what's paid toward principle & interest; the agreement remains the same.