Loan Schedule Macro

[Gregg Wolin]

I am looking to calculate the balance of a revolving loan at any point within a schedule of loan advances and payments.

For Example: The schedule in the attached model includes 3 advances during calendar year 2022. I would like to be able to see the loan balance on a specific date (eg: at the end of each calendar quarter) rather than just on the date of each event. Currently, to get the payoff balance, I create a "Payment" event in the schedule and add amounts from other columns.

I considered creating a daily loan schedule and looking up events by date, but before embarking on that exercise I thought I would reach out to see if anyone has an idea of a more elegant way to accomplish what I am after.

 

[BobBridges]

There's a formula you could use if the interest rate stays constant and the payments were all the same size and at regular intervals. But with varying payments, intervals and interest rates, nope, you just have to track the details row by row.