Advanced loan payoff calculator or formula

[ecmcn]

We have a loan from a family member that we want to pay off early and I'm trying to figure out the exact payoff amount due for a particular date, which isn't a payment date. The loan terms don't fit most of the boilerplate calculators out there - is there an excel formula that will work?

Loan was for 32,500 starting on Jan 5, 2014. It's at 4% compounded annually with annual payments of 3252.96 for 13 years, payments on Dec 31 of each year. We'd like to pay it all off at some date in the near future, and with the annual compounding I'm trying to figure out the exact amount for the date we choose.

Any help appreciated. Thanks!

[MDM]

If you will be satisfied with an ad hoc solution (in other words, you aren't going to make it general enough to handle leap years automatically, etc.), it could go something like:

1.  Calculate the principal remaining after the most recent 12/31 payment.
2.  Multiply that principal by (1.04)^((day of the year on which you would like to pay it off)/(number of days in the year)) to get the total due.

It appears your payment was calculated by assuming the $32,500 gathered interest from 1/4/2014 to 12/31/2014 using $32,500 * (1.04^(360/365)) = $33,781.85.

That amount can then be used in the Excel PMT function: =PMT(4%,13,-33781.85,0,1) gives $3,252.92.  Close enough to $3,252.96?

To calculate the principal remaining after the most recent 12/31 payment, use -CUMPRINC(4%,13,33781.85,1,number of 12/31 payments you have made,1) and subtract that result from $33,781.85.