Table 4S-1FINANCIAL FUNCTIONS FOR SPREADSHEETS
Financial Function Excel Quattro Pro
Irregular Cash Flow Analysis
Net Present Value =NPV(Rate,Values)@NPV(Rate,Block,<Type>)
Internal Rate of Return=IRR(Values,Guess)@IRR(Guess,Block)
Single Payment Compounding
Future Value (F/P,i,n)=FV(rate,nper,pmt,PV,type) @FVAL(Rate,Term,0,PV,<Type>)
Present Value (P/F,i,n)=PV(rate,nper,pmt,FV,type)@PVAL(Rate,Term,0,FV,<Type>)
Effective Interest Rate =RATE(nper,pmt,PV,FV,type,guess)@RATE(FV,PV,Term)
Periods to reach FV =NPER(rate,pmt,PV,FV,type) @CTERM(Rate,FV,PV)
@NPER(Rate,0,PV,FV,<Type>)
Uniform Series Annuities
Annuity Payment(A/P,i,n)=PMT(rate,nper,PV,FV,type) @PMT(PV,Rate,Term)
(A/F,i,n)=PMT(rate,nper,PV,FV,type)@PAYMT(Rate,Term,PV,<FV>,<Type>)
Present Value (P/A,i,n)=PV(rate,nper,pmt,FV,type) @PV(Payment,Rate,Term)
@PVAL(Rate,Term,Pmt,<FV>,<Type>)
Future Value (F/A,i,n)=FV(rate,nper,pmt,PV,type) @FV(Pmt,Rate,Term)
@FVAL(Rate,Term,Pmt,<PV>,<Type>)
Annuity Rate =RATE(nper,pmt,PV,FV,type,guess)@IRATE(Term,Pmt,PV,<FV>,<Type>)
Yield (Bond)=YIELD(See Excel Help screen)@YIELD(See Quattro Help screen)
Annuity Term =NPER(rate,pmt,PV,FV,type)@NPER(Rate,Pmt,PV,<FV>,<Type>)
Periods to reach FV=NPER(rate,pmt,PV,FV,type)@TERM(Payment,Rate,FV)
Uniform Loan Payments
Uniform Loan Payment=PMT(rate,nper,PV,FV,type) @PMT(PV,Rate,Term)
@PAYMT(Rate,Term,PV,<FV>,<Type>)
Remaining Balance=PV(rate,Remain_periods,pmt,FV,type)@PV(Pmt,Rate,Remaining Periods)
Interest in Period =IPMT(rate,per,nper,PV,FV,type) @IPAYMT(Rate,Per,Term,PV,<FV>,<Type>)
Principal in Period =PPMT(rate,per,nper,PV,FV,type) @PPAYMT(Rate,Per,Term,PV,<FV>,<Type>)
Depreciation
Double Declining Bal.=DDB(Cost,Salvage,Life,Period,factor) @DDB(Cost,Salvage,Life, Period)
Straight Line =SLN(Cost,Salvage,Life) @SLN(Cost,Salvage,Life)
SumofYears Digits=SYD(Cost,Salvage,Life,Period) @SYD(Cost,Salvage,Life, Period)
Logic and Miscellaneous Functions
Conditional If-Then =IF(logical_test,value_if_true,value_if_false)@IF(Condition,TrueExpr,FalseExpr)
Horiz. Lookup Table=HLOOKUP(see Excel Help Screen)@HLOOKUP(X,Block,Row_offset)
Vert. Lookup Table=VLOOKUP(see Excel Help Screen)@VLOOKUP(X, Block,Column_offset)
Notes:(1) The formula entered as the Condition in =IF or @IF functions can be any logical expression that can be evaluated as true or false, including compound conditions using #AND# or #OR#.
(2) Microsoft Excel is limited to 7 nested IF functions which requires work-arounds for spreadsheet calculations involving alternatives with unequal lives and/or after-tax cash flow studies.
Hints on Spreadsheet Development:
Whenever possible, input all costs, revenues, interest rates, and so forth as separate cells rather than imbedding them in formulas.
Enter data and analysis formulas in compact areas, preferably organized vertically for efficient recalculation.
Build your worksheet to minimize the number of active columns since each column activates up to 512 rows. Use Page Down to access additional rows.
Do not format blank cells because this procedure makes them active. Avoid using the button in the upper left hand corner of the spreadsheet to select and format an entire worksheet in order to change some property.
Use boxes and/or text and cell colors to highlight input data cells rather than using white space to isolate these sections. This technique makes efficient use of the Home screen.
Values use less memory than formulas. If the output of a formula is not expected to change, the formula can be deleted by using a “values only” command.