University of Richmond University of Richmond
UR Scholarship Repository UR Scholarship Repository
Finance Faculty Publications Finance
1-12-2021
Using Excel to Simulate a Financial Calculator and Excel TVM Using Excel to Simulate a Financial Calculator and Excel TVM
Formulas Formulas
Maura Alexander
Tom Arnold
University of Richmond
Joseph Farizo
Follow this and additional works at: https://scholarship.richmond.edu/6nance-faculty-publications
Part of the Finance and Financial Management Commons
Recommended Citation Recommended Citation
Alexander, Maura, Tom Arnold, and Joseph Farizo. “Using Excel to Simulate a Financial Calculator and
Excel TVM Formulas.”
University of Richmond
, (January 12, 2021): 1-10.
This Working Paper is brought to you for free and open access by the Finance at UR Scholarship Repository. It has
been accepted for inclusion in Finance Faculty Publications by an authorized administrator of UR Scholarship
Repository. For more information, please contact [email protected].
Using Excel to Simulate a Financial Calculator and Excel TVM Formulas
Maura Alexander
The Robins School of Business
Department of Finance
102 UR Drive
University of Richmond, VA 23173
O: 804-287-6497
F: 804-289-8878
Tom Arnold, CFA, CIPM
The Robins School of Business
Department of Finance
102 UR Drive Road
University of Richmond, VA 23173
O: 804-287-6399
F: 804-289-8878
Joseph Farizo
The Robins School of Business
Department of Finance
102 UR Drive
University of Richmond, VA 23173
O: 804-289-8565
F: 804-289-8878
Please do not cite without permission
January 12, 2021
1
Using Excel to Simulate a Financial Calculator and Excel TVM Formulas
Excel is used to build a simulation of the TI BAII-Plus financial calculator to illustrate the
N, I/Y, PV, PMT, and FV inputs. Unlike other financial calculator simulators, this template
also displays the corresponding Excel functions to aid in transitioning the student to using
Excel for financial analysis.
2
INTRODUCTION
While visual presentations of how to use a financial calculator are not new, they
have not evolved much over time. An instructor may choose to use an online simulator
and/or use class time to manually demonstrate how to use a financial calculator, but clear
instruction within the calculator simulation to promote independent work is generally not
provided. Further, the time value of money methodologies differ significantly between
financial calculators and Microsoft Excel, often frustrating students when moving from one
platform to the other as more in-depth financial analysis requires.
The financial calculator simulation proposed in this presentation is programmable
in Excel, provides detailed instruction, and translates the financial calculator functions to
Excel financial formulas. This makes the simulation readily usable in live (or virtual)
classroom settings and as a student resource outside the classroom. Further, instructors can
assign students to develop the calculator, likely in a scaled down version, in a Fintech-
based class to more appropriately assess student understanding of both time value of money
concepts and Excel formulas. The interested reader may also consider Excel/Google
Sheets templates for mortgage analysis (Arnold, Earl, and Marshall, 2017), retirement
analysis (Arnold, Earl, Marshall, and Schwartz, 2017) and student loan analysis (Arnold,
Earl, and Marshall, 2020.)
The basic financial calculator simulation is presented in the next section with
examples of a more advanced version presented in the section that follows. Section three
concludes the presentation.
SECTION 1: TI BAII-Plus Simulation in Excel
3
In Exhibit 1, the TI BAII-Plus financial calculator is simulated using Excel
functions with written instructions within the spreadsheet to allow the student to use the
calculator independently of the classroom. Although the instructions are “basic” in nature,
they are critical in helping the student overcome any apprehension of using the simulation,
and eventually the financial calculator. A further goal might be using Excel financial
functions that are similar to the financial calculator functions (developed in the next
section), but this is not necessary initially.
Exhibit 1: TI BAII-Plus Financial Calculator Simulation in Excel
A
B
C
D
E
F
G
H
I
J
K
1
Financial Calculator
2
3
Enter an “X” in the “ENTRY:” column for the “KEY:” you desire to compute. In the financial
calculator, [CPT] key + [associated “KEY:”] key are pressed after other entries are completed.
4
5
Set cash flows to arrive at the end of the period “END” or at the beginning of the period “BEG”
6
To adjust this setting: [2ND] key + [PMT] key and then switch between “END” and “BEG” using
[2ND] key + [ENTER] key, use [2ND] key + [CPT] key to exit the setting.
7
8
“END” is the default
END
9
10
ENTRY:
KEY:
RESULT:
11
Number of periods (single cash flow), Number of payments (annuity)
10.00
N
12
13
Period-adjusted Interest rate in terms of percentage
5.00
I/Y
14
(e.g. 8% is entered as 8.00 = rate% × 100)
15
Present value
X
PV
-613.91
16
(default sets cash flow to its negative, 100 becomes -100)
17
Constant payment received each period
0
PMT
18
(default sets cash flow to its negative, 100 becomes -100)
19
Future value
1000
FV
20
(default sets cash flow to its negative, 100 becomes -100)
21
Merged cells: A1:B1, A3:I3, A5:I5, A6:I6, A8:F8, A11:F11, A13:F13, A14:F14, A15:F15, A16:F16, A17:F17,
A18:F18, A19:F19, A20:F20
Cells I11, I13, I15, I17, and I19 are set with “font color” as white and “fill color” as black to have the cells
resemble financial calculator keys
Cell K11 formula: =IFERROR(NPER(G13/100, G17, G15, G19, IF(G8=”BEG”, 1, IF(G8=”END”,0,”A”))),” ”)
Cell K13 formula: =IFERROR(RATE(G11, G17, G15, G19, IF(G8=”BEG”, 1, IF(G8=”END”,0,”A”)))*100,” ”)
I
I
I
I
I
I
I
I
4
Cell K15 formula: =IFERROR(PV(G13/100, G11, G17, G19, IF(G8=”BEG”, 1, IF(G8=”END”,0,”A”))),” ”)
Cell K17 formula: =IFERROR(PMT(G13/100, G11, G15, G19, IF(G8=”BEG”, 1, IF(G8=”END”,0,”A”))),” ”)
Cell K19 formula: =IFERROR(FV(G13/100, G11, G17, G15, IF(G8=”BEG”, 1, IF(G8=”END”,0,”A”))),” ”)
Alternative formulas for cells K11, K13, K15, K17, and K19 that will default to end-of-period cash flows
unless cell C8 is set as “BEG”
Cell K11 formula: =IFERROR(NPER(G13/100, G17, G15, G19, IF(G8=”BEG”, 1, 0)),” ”)
Cell K13 formula: =IFERROR(RATE(G11, G17, G15, G19, IF(G8=”BEG”, 1, 0))*100,” ”)
Cell K15 formula: =IFERROR(PV(G13/100, G11, G17, G19, IF(G8=”BEG”, 1, 0)),” ”)
Cell K17 formula: =IFERROR(PMT(G13/100, G11, G15, G19, IF(G8=”BEG”, 1,0)),” ”)
Cell K19 formula: =IFERROR(FV(G13/100, G11, G17, G15, IF(G8=”BEG”, 1, 0)),” ”)
The actual programming is not extensive, but can be tedious (a downloadable
version is available at: https://scholarship.richmond.edu/finance-faculty-publications/X/).
In the exhibit, a calculation is displayed for the present value of $1,000 received ten
periods in the future assuming a periodic interest rate of 5%.
Although one may be tempted to omit some of the instructions or information
presented in Exhibit 1, one should be careful about making changes. The fact that
payments are set to the “end of period” at the top of the exhibit is to emphasize a setting
students often neglect. Further, having numerical entries to the left of the financial
calculator key is to emphasize the value must be inputted into the calculator first before
assigning the value to the associated input key. Other pieces of information are also
critical; for example, periodic interest is entered as a whole number assumed to be a
percentage and certain values are assumed negative even through a positive number is
entered. These are the “small things” that students find baffling initially.
Once the simulation is presented, it is easy to demonstrate time value of money
computations. The simulation could be displayed in Zoom or in the classroom and then
5
utilized within a live/virtual class when a need arrives. For example, one can easily
demonstrate how a bond’s yield decreases as its price increases. In Exhibit 2, a ten-year
$1,000 bond with 6% annual coupons (i.e. $60.00 = 6% × $1,000) that sells for par (i.e.
$1,000) is displayed. From Column K in the simulator, the result demonstrates that the
yield is 6.00% in this scenario.
Exhibit 2: Financial Calculator Simulation in Excel with a Bond
A
B
C
D
E
F
G
H
I
J
K
1
Financial Calculator
2
3
Enter an “X” in the “ENTRY:” column for the “KEY:” you desire to compute. In the financial
calculator, [CPT] key + [associated “KEY:”] key are pressed after other entries are completed.
4
5
Set cash flows to arrive at the end of the period “END” or at the beginning of the period “BEG”
6
To adjust this setting: [2ND] key + [PMT] key and then switch between “END” and “BEG” using
[2ND] key + [ENTER] key, use [2ND] key + [CPT] key to exit the setting.
7
8
“END” is the default
END
9
10
ENTRY:
KEY:
RESULT:
11
Number of periods (single cash flow), Number of payments (annuity)
10.00
N
12
13
Period-adjusted Interest rate in terms of percentage
X
I/Y
6.00
14
(e.g. 8% is entered as 8.00 = rate% × 100)
15
Present value
-1000
PV
16
(default sets cash flow to its negative, 100 becomes -100)
17
Constant payment received each period
60
PMT
18
(default sets cash flow to its negative, 100 becomes -100)
19
Future value
1000
FV
20
(default sets cash flow to its negative, 100 becomes -100)
21
When the bond price is increased to $1,050 by modifying cell G15, the yield to maturity
decreases to 5.34%, which is shown in the result column K of the simulator in Exhibit 3.
Exhibit 3: Financial Calculator Simulation in Excel with an Increased Bond Price
A
B
C
D
E
F
G
H
I
J
K
1
Financial Calculator
2
3
Enter an “X” in the “ENTRY:” column for the “KEY:” you desire to compute. In the financial
calculator, [CPT] key + [associated “KEY:”] key are pressed after other entries are completed.
4
I
I
I
I
I
I
I
I
I
I
I
6
5
Set cash flows to arrive at the end of the period “END” or at the beginning of the period “BEG”
6
To adjust this setting: [2ND] key + [PMT] key and then switch between “END” and “BEG” using
[2ND] key + [ENTER] key, use [2ND] key + [CPT] key to exit the setting.
7
8
“END” is the default
END
9
10
ENTRY:
KEY:
RESULT:
11
Number of periods (single cash flow), Number of payments (annuity)
10.00
N
12
13
Period-adjusted Interest rate in terms of percentage
X
I/Y
5.34
14
(e.g. 8% is entered as 8.00 = rate% × 100)
15
Present value
-1050
PV
16
(default sets cash flow to its negative, 100 becomes -100)
17
Constant payment received each period
60
PMT
18
(default sets cash flow to its negative, 100 becomes -100)
19
Future value
1000
FV
20
(default sets cash flow to its negative, 100 becomes -100)
21
From here, perhaps an instructor can begin a discussion on how a central bank has to
engage in market transactions to “set” interest rates or how a bond with relatively high
coupons becomes more desirable as interest rates fall, making its price increase until the
yield deceases to a level equivalent to current market rates. Many other visual
presentations of finance concepts are certainly possible.
SECTION 2: Transition from the Financial Calculator to Excel
In Exhibit 4, programming is introduced to the right of the financial calculator
simulation to display the corresponding Excel financial function. This programming is
tedious and repetitive, however, as mentioned previously, a downloadable version of the
calculator is available.
Exhibit 4: Financial Calculator Simulation with Excel Formulas
G
H
I
J
K
L
M
N
O
P
Q
8
END
9
10
ENTRY:
KEY:
RESULT:
EXCEL FORMULA:
11
10.00
N
12
~
7
13
5.00
I/Y
14
15
X
PV
-613.91
= PV ( 5% , 10 , 0.00 , 10,000.00 , 0 )
16
= PV ( I/Y , N , PMT , FV , 0 )
17
0
PMT
18
19
1000
FV
20
21
Merged cells: M11 through Q11, M12 through Q12, M13 through Q13, M14 through Q14, M15 through Q15, M16
through Q16, M17 through Q17, M18 through Q18, M19 through Q19, M20 through Q20
Cell M11: =IF(K11=” “,” “,CONCAT(“= NPER ( “,G13,”% , “,TEXT(G17,”#,##0.00”),” , “
,TEXT(G15,”#,##0.00”),” , “,TEXT(G19,”#,##0.00”),” , “,IF(G8=”BEG”,1,0),” )”))
Cell M12: =IF(K11=” “,” “,CONCAT(“= NPER ( I/Y , PMT , PV , FV , “,IF(G8=”BEG”,1,0),” )”))
Cell M13: =IF(K13=” “,” “,CONCAT(“= RATE ( “,G11,” , “,TEXT(G17,”#,##0.00”),” , “
,TEXT(G15,”#,##0.00”),” , “,TEXT(G19,”#,##0.00”),” , “,IF(G8=”BEG”,1,0),” )*100”))
Cell M14: =IF(K13=” “,” “,CONCAT(“= RATE ( N , PMT , PV , FV , “,IF(G8=”BEG”,1,0),” )*100”))
Cell M15: =IF(K15=” “,” “,CONCAT(“= PV ( “,G13,”% , “,G11,” , “,TEXT(G17,”#,##0.00”),” , “
,TEXT(G19,”#,##0.00”),” , “,IF(G8=”BEG”,1,0),” )”))
Cell M16: =IF(K15=” “,” “,CONCAT(“= PV ( I/Y , N , PMT , FV , “,IF(G8=”BEG”,1,0),” )”))
Cell M17: =IF(K17=” “,” “,CONCAT(“= PMT ( “,G13,”% , “,G11,” , “,TEXT(G15,”#,##0.00”),” , “
,TEXT(G19,”#,##0.00”),” , “,IF(G8=”BEG”,1,0),” )”))
Cell M18: =IF(K17=” “,” “,CONCAT(“= PMT ( I/Y , N , PV , FV , “,IF(G8=”BEG”,1,0),” )”))
Cell M19: =IF(K19=” “,” “,CONCAT(“= FV ( “,G13,”% , “,G11,” , “,TEXT(G17,”#,##0.00”),” , “
,TEXT(G15,”#,##0.00”),” , “,IF(G8=”BEG”,1,0),” )”))
Cell M206: =IF(K19=” “,” “,CONCAT(“= FV ( I/Y , N , PMT , PV , “,IF(G8=”BEG”,1,0),” )”))
The benefit of viewing the equivalent Excel financial function (and generally, the
same function in Google Sheets) relative to the financial calculator is not available in
other simulations. Depending on the instructor’s intentions, this portion of the simulation
can become part of a classroom exercise or provided as a resource for students to try the
Excel programing on their own.
Further, in an online testing situation, it may be beneficial to have students
compute answers to test problems using Excel and Excel functions rather than with a
financial calculator in order to send the instructor a file with computational work for
8
partial credit. If this is the case, there is a great benefit for using the simulation to help
students make the transition to Excel from the financial calculator.
SECTION 3: Conclusion
In this paper, Excel is used to generate a simulation for the TI BAII-Plus financial
calculator. While the simulation is annuity-based and does not reproduce all of the
functions of the financial calculator, it is appropriate for introducing the financial
calculator to students while helping students transition from the financial calculator to
Excel or Google Sheets financial functions.
The simulation is very visual and includes instructions as part of the template. If
desired, instructors may print Exhibit 2, to provide as a handout for getting started with
the financial calculator. Similarly, the Excel file can be given to the student as a resource.
Further, within the classroom setting (live or virtual), the simulation can be readily
available for the instructor to demonstrate the proper use of the financial calculator or to
demonstrate financial concepts such as the relationship between a bond yield and its
price.
9
REFERENCES:
Arnold, Tom, John Earl, and Cassandra Marshall. 2017. “Using Google Sheets to
determine mortgage information.” Journal of Wealth Management, 19:4, 128-131.
Arnold, Tom, John Earl, Cassandra Marshall, and Adam Schwartz. 2017. “Excel
calculators for determining retirement accumulation and disbursement information.”
Journal of Wealth Management, 20:2, 94-101.
Arnold, Tom, John Earl, and Cassandra Marshall. 2020. “An Excel calculator for
determining student loan information.” Journal of Wealth Management, 22:4, 133-139.
