Post Reply 
17BII, 27S: doing TVM calculations inside solver equations
07-06-2019, 01:00 AM
Post: #9
RE: 17BII, 27S: doing TVM calculations inside solver equations

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,

(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:


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.
Visit this user's website Find all posts by this user
Quote this message in a reply
Post Reply 

Messages In This Thread
RE: 17BII, 27S: doing TVM calculations inside solver equations - Peter A. Gebhardt - 07-06-2019 01:00 AM

User(s) browsing this thread: 1 Guest(s)