Jul 07 2022 04:40 PM - edited Jul 07 2022 04:52 PM
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...
Douglas
Jul 08 2022 12:19 AM - edited Jul 08 2022 12:20 AM
@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.
Jul 08 2022 07:57 PM