Forum Discussion
Formula to extract principal portion of a principal/interest loan pmt at 111 mos. of 360 mo. loan
I have the need to pull the principal portion of a fully amortizing principal and interest based 360 month loan schedule at the 111th month and instead of going into great detail here will post a cou...
Douglas997t As I understood, from your example, you need to calculate the principal portion of the 111th payment. Although VBA is not my strong point, I gave it a shot anyway, and wrote a fairly straight-forward user defined function called Nprincipal that takes four variables (the initial amount, the rate, the amount calculated by PMT and the period you want to calculate the principal payment for.
Function Nprincipal(amt, rate, pmt, n)
For i = 1 To n
interest = amt * rate / 12
principal = pmt - interest
amt = amt - principal
Next i
Nprincipal = principal
End Function
See if this works for your. I've included the code in your workbook (now an xlsx file) and entered a formula in E10 on the amortisation schedule, using this function.
- Thanks Riny! I really appreciate your efforts on this!
