HP 17BII+ TVM Solver Solution expressing the Balloon Payment as a Percentage with Begin and End mode [and Down Payment as a percentage]

The Museum of HP Calculators

HP Articles Forum

[Return to the Index ]
[ Previous | Next ]

HP 17BII+ TVM Solver Solution expressing the Balloon Payment as a Percentage with Begin and End mode [and Down Payment as a percentage]

Posted by bjmaskell on 3 Mar 2012, 12:36 a.m.

HP 17BII+ Solver Solution

Description

This formula is adapted from page 199 of the Hp 17bii+ (silver) manual.

It partially recreates the TVM (Time Value of Money) solution in the solver.

It automates the balloon payment to be a percentage rather than a fixed $dollar amount which otherwise would be calculated manually each time once the PV (loan amount) is known, by altering the FV this then affects the PMT amount. Hence the standard HP TVM function make 'what if' scenarios tedious at best when working from a fixed monthly budget scenario.

The following solver formula enables the HP17BII+ to become a 'what if' scenario calculator.

Advantanges

The formula expresses the Balloon payment as a percentage rather than a $dollar amount. Hence the [PV] loan amount and [FV] balloon payment are both calculated automatically. By altering the balloon [RESD%], and lease period [N] will estimate a maximum loan amount [PV] based upon a persons fixed monthly budget [PMT].

Example

You may wish to purchase a car on finance, you can afford $450 per month, the finance interest is 8%, and the car dealer has a 36 month lease with up to a 40% residual, a 48 month lease up to 30% residual and a 60 month lease up to 20% residual.

Speed of Solver

You can speed up the Solver by 'guessing' - entering in a low and high guess i.e: $20,000 [PV] $50,000 [PV] [PV]

Solver Formula

Definitions:

Definitions of Key Inputs:

PMT = Payment per period
PV = Present Value (or loan amount)
RSD% = Residual (or balloon payment) expressed as an Integer (i.e: 20 = 20%). RESD% is also referred to as the FV or future value expressed as a %
DWN% = Down Payment as a percentage of the Loan Amount (PV).
P/YR = The number of periods (or payments) per year
I%YR = Interest Rate Per Year
N = Length of loan expressed as a total (i.e: a 5 year loan x 12 P/YR (payments per year), N = 60)
BEG? = When payments are made at the end of the each period enter 0 into BEG?, when payments are made at the beginning of each period enter 1 into BEG?.

Version 1 - The Original Solver Formula:

FINANCE.CALC: PMT = ( - PV - ( PV x ( RSD% ÷ 100 )) x ( SPPV(I%YR ÷ P/YR : N )) ÷ (USPV(I%YR ÷ P/YR:N - #ADV ) + #ADV)

Version 2 - Improved Version

Improved Solver Formula: - This formula contributed by Peter A. Gebhardt

FINANCE.CALC: 0 x BEG? x N x #P x I%YR x PV x PMT + PMT = (-PV + PV x (RSD% ÷ 100) x SPPV(I%YR ÷ #P:N ) ÷ ( USPV( I%YR ÷ #P:N - BEG? ) + BEG? )

Version 3 - With Downpayment as a percentage

Improved Solver Formula with ability for Down Payment as a percentage of the loan amount (PV): - This formula also contributed by Peter A. Gebhardt

RSD%, DWN% as a percentage of PV : 0 x BEG? x N x #P x I%YR x PV x DWN% x PMT + PMT = ( -PV + PV x DWN% ÷ 100 + PV x ( RSD% ÷ 100 ) x SPPV( I%YR ÷ #P:N ) ) ÷ ( USPV( I%YR ÷ #P:N - BEG? ) + BEG? )

Scenario Test:

Mary wishes to buy a car using a lease arrangement. What is the maximum loan amount Mary can borrow on a budget of $450 per month. The current finance interest is 8%, and the car dealer has 3 options: (1) - a 36 month lease with up to a 40% residual; (2) - a 48 month lease up to 30% residual and; (3) - a 60 month lease up to 20% residual.

Calculate the maximum purchase price that Mary can spend on a car (Maximum Loan Amount) for each scenario.

Initial Inputs:

P/YR = 12 (Payments per year)
#ADV = 1 (Payments are monthly in advance)
PMT = -450 (Buyers monthly budget amount)
Scenario 1 Inputs:
N = 36 (Length of loan expressed as a total (i.e: a 3 year loan x 12 P/YR (payments per year))
RESD% = 40 (Maximum residual balloon payment allowed under this loan period)
[PV] : Result $21,100.70 Note: You can increase the speed of the solver by entering guesses: i.e: 15000 [PV] 30000 [PV] [PV]
Scenario 2 Inputs:
N = 48 (Length of loan expressed as a total (i.e: a 4 year loan x 12 P/YR (payments per year))
RESD% = 30 (Maximum residual balloon payment allowed under this loan period)
[PV] : Result $23,730.89
Scenario 3 Inputs:
N = 60 (Length of loan expressed as a total (i.e: a 5 year loan x 12 P/YR (payments per year)
RESD% = 20 (Maximum residual balloon payment allowed under this loan period)
[PV] : Result $25,805.42
Tested on:
HP 17BII+ (Silver)

VERSION HISTORY

UPDATE 03 MAY 2012 - IMPROVED VERSION SUGGESTION FROM Peter A. Gebhardt - THANKS PETER

There is a slight error in your formula - and a potentially misleading use of a variable name. Some cosmetics I've added as well.

Think about using BEG (or even better BEG?) instead of #ADV, because #ADV is very often used already for the number (#) of advanced payments in Leasing calculations.

The corrected formula looks like this:

0*BEG?*N*#P*I%YR*PV*PMT

+PMT

(-PV+PV*(RSD%/100)*SPPV(I%YR/#P:N)

(USPV(I%YR/#P:N-BEG?)+BEG?)

The "0*BEG?* ..." part gives you a chance for ordering the input values in a way conforming to the other TVM menus of the HP17bII+.

Solver Formula with Down Payment as a percentage : This formula contributed by Peter A. Gebhardt

RSD%, DWN% as a percentage of PV : 0 x BEG? x N x#P x I%YR x PV x DWN% x PMT + PMT = (-PV+PV x DWN% ÷ 100 + PV x (RSD% ÷ 100) x SPPV(I%YR ÷ #P:N)) ÷ (USPV(I%YR/#P:N-BEG?)+BEG?)

Edited: 4 May 2012, 6:49 a.m.

[ Return to the Message Index ]

Go back to the main exhibit hall