excel future value fv (rate,nper,pmt,pv,type). annual interest rate divided by the number of...

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?