# Thread: Calculating Mortague Principle Due (Excel 97)

1. ## 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

2. ## 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 '

3. ## 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.

4. ## Re: Calculating Mortague Principle Due (Excel 97)

NOT IN THE REAL LIFE...
The function you mention - CUMPRIN() - deals with loans, not real-life 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 real-life mortgage, contact your financial institution.

5. ## Re: Calculating Mortague Principle Due (Excel 97)

You are right - thank you

6. ## 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.

7. ## 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

8. ## 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•