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 couple of images and a spreadsheet that depict and better explain what I need. The solution needs to be a one cell one formula solution as opposed to building a table as the calculations made using this data need to be permanently par of each row's data within the sheet, you'll see why when you look at the images.


Thanks in advance...


2 Replies

@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!