Excel Future ValueExcel Future Value
FV(rate,nper,pmt,pv,type)FV(rate,nper,pmt,pv,type)
FV(rate,nper,pmt,pv,type)
Annual interest rate divided by thenumber of compound periods
FV(rate,nper,pmt,pv,type)
Number of compounding periods typically (years * months)
FV(rate,nper,pmt,pv,type)
Additional “new” money each period
Present ValueThe initial deposit
FV(rate,nper,pmt,pv,type)
1 if the payment is at the start of the period0 if the payment is at the end of the period
FV(rate,nper,pmt,pv,type)
FV(rate,nper,pmt,pv,type)
Excel requirement 1:
This value must be calculated by the users before it can be entered into the formula
FV(rate,nper,pmt,pv,type)
Annually? Semi annually? Quarterly? Monthly? Daily?
THINK!
Should the users be burdened with making this mental calculation?
FV(rate,nper,pmt,pv,type)
Excel requirement 2:
This value must be calculated by the users before it can be entered into the formula
Excel expects the users to remember to multiply (years * compoundingperiods) to obtain this number
FV(rate,nper,pmt,pv,type)
THINK!
Should the users be burdened with remembering this rule?
FV(rate,nper,pmt,pv,type)
Excel requirement 3:
The users must remember to enterthese values as negative numbers
FV(rate,nper,pmt,pv,type)
WHY?
Excel requirement 4:
If you make the payment at the beginning of the month, enter a 1
If you make the payment at the end of the month enter a 0
FV(rate,nper,pmt,pv,type)
Collecting all of the Excel rules:
1 Compute periodic interest rate first2 Compute number of compounds next3 Remember to enter negative amounts4 Remember the 1 and 0 rule
Why burden the users with remembering all of these rules?
When designing spreadsheets,it is your responsibility to design them so that the users do not have to remember these rules.
Users want correct answers in a minimal amount of time.
When designing for usability...
…ask simple questions that:
• your users can answer• don’t require mental math• don’t require value judgments
Think like your users, but
do not expect them to: • think like you• act like you• know what you know
How can you, as the spreadsheet developer, apply your knowledge to design easy to use spreadsheets?
The rate can be determined in two simple questions
What is the annual interest rate?
How many compound periods per year?
annual rate / periods = rate rate
The nper can be determined in two simple questions
How long is the investment in years?
How many compound periods per year?
Years * periods = nper nper
How much is each monthly deposit?
DO NOT ask your users to, “Enter your monthly deposit as a negative number.”
Put the negative sign in the formula
=FV(rate,nper,-pmt,pv,type)
How much is your first deposit?
DO NOT ask your users to, “Enter your first deposit as a negative number.”
Put the negative sign in the formula
=FV(rate,nper,-pmt,-pv,type)
If you make your first additional deposit on the first day of the month, enter 1
If you make your deposit on the last day of the month, enter 0
=FV(rate,nper,-pmt,-pv,type)
Go to thespreadsheet
WHY?