I was trying to find a financial function that would calculate the total principal paid on a loan through a certain date. The closest I could find was the PPMT function, but that only shows the principal payment for one period, not cumulative. I used the array formula below to make it cumulative.
Whenever financial functions like PPMT are used, the most common mistake is keeping the periods consistent. Note that I convert the annual interest rate to monthly by dividing by 12 and the annual periods to monthly by multiplying by 12.
The ROW(INDIRECT(“1:”&C6)) part gives me an array of numbers from 1 to 36 (in this example). PPMT finds the principal payment for each of those periods and the SUM fucntion sums them. Don’t forget to use Control+Shift+Enter to make it an array formula.
If this is a mortgage, then after three years of making payments, you would still owe $154,000. Yikes!
Thanks, Jeff, for the suggestion.
Hi Dick,
I thought this was a really good post. If you wouldn’t mind, could you sometime in the future expand on the use of the ROW and INDIRECT worksheet functions to build arrays (one and two dimensions)? I’ve read your previous post on the INDIRECT function as well as studied books and other sites but for whatever reason I still can’t seem to fully grasp the concept.
Thanks again.
John Mansfield
Much simplar way to do this:
Install the Analysis Toolpak, and use the CUMPRINC function.
My apologies for misspelling “simpler”.