CODEWITHSUNDEEP
vbaexcel
theteague
theteague

Reputation: 413

Excel Formula (or VBA) to Calculate Remaining Interest on Loan mid-term

Good afternoon!

I have 13000 loan records. My sheet has the following headers | OrigLoanAmt | RemainingBalance | OrigLoanTermInMonths | RemainingTermInMonths | RegularMonthlyPmt | InterestRate |

What is a formula or VBA that will take the information I have, throw it into an amortization table, look at the current loan period, and output the total remaining interest scheduled on a loan assuming the borrower has made, and will continue to make, the minimum monthly payment for the entire term?

Example: 19 months ago I borrowed $30,000 on a 48 month term with a 2.99% rate. After I make my next payment, my new balance will be $17,933.87. From that point, per the amortization schedule, I will still have $655.19 interest to pay over the remaining 28 months.

So, my question restated would be: What formula, or VBA script, could I use to calculate that $655 figure for 13,000 loan records?

Thank you very much for your consideration on this issue!!

Upvotes: 1

Views: 998

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

You would use the CUMIPMT() Function

=CUMIPMT(0.0299/12,48,30000,21,48,0)

Upvotes: 2

Related Questions