In my first blog post back in August, I had a formula that would solve for future value given dates and amounts. In this post we will use the same formula to solve for the last payment due. There are a number of scenarios where the last payment might be needed.
Assume for a moment that you won a court judgment against someone for $100,000 back on 9/15/2006. The judgment also stated that you would receive 10% compound annually (APY) until the $100,000 is fully paid. Through the years you have received payments on various dates (see example below). It is now 2014 and you get a notice that the other party wants to pay off the judgment in full on 12/4/2014. You will need the final principal and interest that will have earned you 10% throughout that period.
Future Value = SUMPRODUCT((1+$E$4)^((MAX($D$7:$D$22)-$D$7:$D$22)/365)*$E$7:$E$22)
Last Payment =-SUMPRODUCT((1+$E$4)^((MAX($D$7:$D$22)-D$7:$D$22)/365)*$E$7:$E$22)
As you can see, the formulas are exactly the same, except the last payment formula has a negative sign in front, in order to make the final payoff a positive number. The difference is how the cash flows are entered. In this case, the judgment amount and date are entered first and the judgment amount is negative. Payments are positive. The last payment date on 12/4/2014 is entered as zero. So far you have received $111,250.00, so if you never received another payment, you have received the principal and $11,250 in interest, which amounts to an APY of 6.2688%. You however, want the additional interest up until 12/4/2014 that would return 10% for the whole period.
In effect, you want to know the future value of a stream of payments earning 10%, starting $100,000 in the hole, but with a stream of positive cash flows. That future value is $12,462.23.
Download workbook “SolveLastPayment” from:
Downloads Written in Excel 2013