17BII, 27S: doing TVM calculations inside solver equations

[Peter A. Gebhardt]

Dave,

wouldn't it be easier to use some of the existing SOLVER examples for Wrap-Around Loans or Extra Payments - or Skipped Lease Payments - as inspirating starting points? (All programs are in the support docs on the Museum DVDs)

Then you can use any point in time during the life of the loan to add an extra payment an calculate your total interest savings.

Best regards,
Peter

(05-01-2019 12:58 PM)Dave Britten Wrote:  I'll explain the specific problem in more detail, in case there's an easier way to do it that doesn't involve TVM.

I'm trying to make a solver formula that will calculate the amount of interest saved over the remaining life of a loan if I were to make an extra principal pay-down of a given size. This is the general calculation process that I use:

1. Make sure N, I%, PV, PMT, and FV reflect the current (or opening) state of the loan.
2. Subtract the extra principal payment from PV, and update PV.
3. Recalculate N to determine the new number of payments remaining to pay off the reduced principal. This will probably be a non-integer value, i.e. an odd payment at the end.
4. Recalculate FV using FLOOR(N) to determine the remaining balance for the final odd period.
5. Calculate the total interest paid on the original principal using the original values in "N*PMT+PV" (gives a negative value representing total interest paid).
6. Calculate the total interest paid on the reduced principal, using the new values of N, PV, and FV in "PMT*FLOOR(N)+FV*(1+I%/100)+PV" (multiplies the payment amount by the number of full payments, and calculates the periodic interest on the final odd period).
7. Subtract the original interest (step 5) from the reduced interest (step 6) to obtain the total savings (a positive number).

As an example, suppose you've got a loan with these properties:

N=360
I%/YR=4.75%
PV=141,000
PMT=-735.52
FV=0

The total interest payments on the loan would be ($123,788.19). If you paid an extra $1,000 in principal, then N becomes 354.41, and the total interest paid is ($120,679.83), giving a total savings of $3,108.36.