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.