Mathematics of Finance Guidelines Using Excel
Section 1 – Simple Interest
A. Present Formulas in Section 8.1 of text
B. Computer usage (Excel)
1. Future value section – have students insert formulas for computing Interest Earned and
Future Value in the appropriate cells. Then use these formulas for sample exercises.
CAUTION: 1) In this and in all future work in this chapter, interest rates should be entered
as decimals. Thus, 6% should be entered as .06.
2) When entering Time (years) as fractions of a year, Excel requires the
fractions to be entered as formulas. Thus, 21 months should be entered on the
formula line as =21/12 or else as 1.75.
2. Present Value Section – have students insert formulas for computing Amount to be
Invested and the Interest Earned in the appropriate cells. Then use these formulas for
sample exercises.
3. Treasury Bills – Simple Discount – Have students create a set of columns similar to the
Future Value and Present Value sections and insert the appropriate formulas. Then use
this table for sample exercises.
Section 2 – Compound Interest
A. Derive the Compound Interest Formulas given in Section 8.2 of the text.
B. Computer usage (Excel)
Future Value
1. Have the students insert a formula for computing the Periodic Interest
(annual interest compounding periods per year, or B5/C5.)
2. Now have the students insert the formula for Future Value Compound Interest
in the last column and use this table for sample exercises.
Present Value
3. Have the students insert the formula for computing the Periodic Interest.
4. Now have the students insert the formula for Present Value Compound Interest
in the last column and use this table for sample exercises.
Section 3 – Annuities and Sinking Funds
A. Derive the formulas for Ordinary Annuities and Sinking Funds, given in Section 8.3 of the
text.
B. Computer usage (Excel)
Comment – Both of these formulas have been pre-entered. While not difficult, they both
involve careful use of parentheses, and we did not want students to get bogged down here.
You should show them the formulas as entered on the spreadsheet, however, and discuss
how they got there.
Ordinary Annuity
This is in the lower half of the "Annuities" tab.
By careful use of the values copying function and the formulas copying function, students
can create an annual annuity result table like that on Page 578 of the text. To do this, for the
initial "Number of Years" (cell E15), input the number 1. In the cell directly below, put in
the formula =1+E15. Then copy that formula to the desired number of cells directly below.
Now copy and paste the entries in the first four columns of this section (A15 – D15) to the
corresponding rows. Finally, copy the Future Value formula to the same corresponding
rows. This exercise will give the students good practice in using the "copy" function of
Excel, both for constant values and for formulas.
Sinking Fund
As with the Ordinary Annuity formula, this formula has been pre-entered. However, you
should go over its entry with the students.
Section 4 – Present Value of an Annuity and Amortization
A. Present Value of an Annuity – (Worksheet Annuity – PV) This formula provides the answer
to the question, "How much should be deposited into an annuity now to provide given equal
periodic payments each period (month) for n years, beginning with the next period (month)?"
This formula has been pre-entered in the Excel package, due to the complexity of
parentheses. However, you should show the students the formula and how it got there.
B. Amortization
1. Loan Payments – This (top) portion of the Amortization worksheet contains a variation of
the "Amortization of a Loan" formula on Page 591 of the text. Specifically, what should be
the equal periodic payments, R, to repay a loan of P dollars? Discuss the formula in cell F4
with the students.
2. Amortization Table – This allows the student to construct a (monthly) amortization table
amortize a loan. The formula A10 + 1 has pre-entered in cell A11. Simply copy this formula
to as many cells directly below as months are desired. Similarly, all other entries in this
section have been entered as formulas relating to the "Loan Payments" formula preceding.
Hence, the entries in these columns can be copied as formulas to the corresponding cells
directly below.
Comment: In the amortization table above, it is assumed that payments are monthly.
However, other units of payment work just as well. For example, in the event of quarterly
payments, the word "Month" represents "Quarter."