CORE FINANCE

SPREADSHEETS
This note is some basic information that should help you get started and do most
calculations if you have access to spreadsheets. You could also use a regular calculator
and formulae and do the calculations, but it will take a much longer time. A compromise
solution is a financial calculator, which can do most stuff. But I strongly encourage you to
start using spreadsheets right away, and use a financial calculator only if you have to.

This note is primarily some brief hints on spreadsheets; you are responsible for starting
here and learning as much as you need to. Since financial calculators have different
models, I will just talk about some aspects of the simplest one HP10BII. Again, you are
responsible for learning how to use a calculator if you do not have access to a
spreadsheet.

SPREADSHEETS

Important Stuff:

1. Make sure there are at least two decimals allowed in each cell. Otherwise
rounding off may create problems in a multi-step problem.
2. Always enter the interest rate values in decimals because that is what a
spreadsheet/excel wants. So, if the interest rate is 10%, enter 0.10.
3. Make sure you get comfortable with the financial functions as early as
possible.

Future Value (FV), Single Cash flow today

In a cell type in =FV( to bring up the formula.

To complete the calculation you will need to have the following information:
Rate- This is the discount rate you are using. It is also called the interest rate, cost of
capital, etc. These are always reported annually. It is important to use the rate per period
according to the periodicity of your problem. For example if your annual discount rate is
10%, and the periods are measured in years, the rate to be entered is 0.10. If
compounding is monthly, however, you will enter.10/12 for the rate
Nper- This is the number of periods. This matches the periodicity of your problem. So if
you have entered an annual interest rate because your periods are each one year long,
then enter the number of years. But if compounding is monthly, and therefore you are
using the monthly interest rate, the number of periods will be 12 times the number of
years
Pmt- Enter 0 if you are doing the FV of a single cash flow
Pv- Enter the value today whose FV you are calculating
Type- NOT REQUIRED This indicates whether the payment is due at the beginning of the
period (type 1) or the end of the period (type 0). Type 0 is the default assumption and no
entry is required when this is the case.

2
Example- What is the FV of $100, 10 years from now, if the annual interest rate is 10%.
You would enter =FV(.10,10,0,100) and get an answer of -$259.37. Suppose compounding
is monthly (this will be obvious in real life; for example if you make monthly payments on
a loan, you can assume that compounding is monthly). Then you will enter
=FV(.10/12,120,0,100) to get -$270.70. (As you will learn, this is a higher number because
of compounding). Note that excel returns a negative number if you enter a positive
number, and vice versa. It is because even a calculator knows that you cannot get
something for nothing! You know whose point of view you are taking, and can correct this
by placing a negative sign either at the beginning of the equation or before the payment
amount if it is an outflow for you.

Future Value (FV), Annuity

In a cell type in =FV( to bring up the formula.

To complete the calculation you will need to have the following information:
Rate- See above
Nper- See above
Pmt- This is the amount of each payment in case of an annuity.
Pv- 0
Type- NOT REQUIRED This indicates whether the payment is due at the beginning of the
period (type 1) or the end of the period (type 0). Type 0 is the default assumption and no
entry is required when this is the case.

Example- Suppose you have a 10-year annuity that pays $120 per year and you have an
annual discount rate of 8%. To calculate in excel you would enter =FV(.08,10,120) and get
an answer of $1,738.39. This means that the total value of all your annuity payments is
$1738.39 in year 10 dollars.

Now let’s say that you have the same 10-year annuity, but you receive month payments of
$10, instead of annual payments of $120. To calculate in excel, enter =FV(.08/12,120,10).
Note that the FV is now $1,829.46.

Present Value (PV), Single cash flow

In a cell type in =PV( to bring up the formula

To complete the calculation you will need to have the following information:
Rate- See above
Nper- See above
Pmt- 0
Fv- Amount of money in the future whose PV you are calculating
Type- NOT REQUIRED This indicates whether the payment is due at the beginning of the
period (type 1) or the end of the period (type 0). Type 0 is the default assumption and no
entry is required.

3
Example- What is the PV of $1,000 received 10 years from now if the interest rate is 5%.
To calculate in excel you would enter =PV(.05,10,0,1000) and get an answer of $613.91.
This means that the value of the $1,000 is $613.91 in today’s dollars. Now let’s say that
you have monthly compounding, you will enter =PV(.05/12,120,0,1000). Note that the PV
is now $607.16.

Present Value (PV), Annuity

In a cell type in =PV( to bring up the formula

To complete the calculation you will need to have the following information:
Rate- See above
Nper- See above
Pmt- Enter the amount of the annuity (if the amount changes every year, use NPV
function; see below)
Fv- Not required
Type- NOT REQUIRED This indicates whether the payment is due at the beginning of the
period (type 1) or the end of the period (type 0). Type 0 is the default assumption and no
entry is required.

Example- You have a 10-year annuity that pays $120 per year and you have an annual
discount rate of 8%. To calculate in excel you would enter =PV(.08,10,120) and get an
answer of $805.21. Now let’s say that you have the same 10 year annuity, but you receive
month payments of $10, instead of annual payments of $120. To calculate in excel, enter
=PV(.08/12,120,10). Note that the PV is now $824.21.

Payment (Pmt) Corresponding to a Single Cash Flow, Now or in the Future

In a cell type in =PMT( to bring up the formula

To complete the calculation you will need to have the following information:
Rate- See above
Nper- See above
Pv- This is present value of the loan or project, if that is what you know (then Fv will be
zero)
Fv- 0 if you have entered the Pv above, otherwise the value in the future for which you
need to calculate PMT
Example 1: You are offered a loan of $10,000 at an annual interest rate of 5% over 10
years. To calculate your payment type =PMT(.05,10,10000) and you should get an answer
of $1,295.05. This means that you would have 10 equal payments of $1,295.05 each year
in order to repay the loan in full.

Example 2: You want to know how much to save each year for the next 5 years to have
$5,000 to buy a car, if the interest rate is 6.5%. You will type in =PMT(.065,5,0,5000) and
get $878.17.

4
Net Present Value (NPV)

In a cell type in =NPV( to bring up the formula

To complete the calculation you will need to have the following information:
Rate- See above
Values- This is a list of all of the future cash flows
Initial Investment- This is the value of the inflow or outflow at time zero (now). This
amount is added back in after taking the discounted PV of all future cash flows. DO NOT
include the initial investment into the values because excel will incorrectly apply the
discount rate.

Example: Let’s say you have an investment opportunity where you can invest $100 now
with expected future annual cash flows of $10, $25, $40, and $50 in years 1-4. Your cost of
capital (discount rate) is 10%. You would enter your formula as =NPV(.1,10,25,40,50)-
100. The initial investment is subtracted because it is a negative cash flow. The NPV for
this opportunity is then -$6.04 and is a poor investment. A better way to do this because it
allows you change stuff, you can enter each cash flow in a horizontal series of cells and
reference those cells in your formula. Enter -100, 10, 25, 40, 50 in cells A1:A5, for
example, then enter your formula as =NPV(.1,A2:A5)+A1. You should get the same
answer. This method is preferred because you first need to calculate cash flows in many
situations and then you can replace A2:A5 and A1 by the cells in which the cash flows
reside.

Internal Rate of Return (IRR)

In a cell type in =IRR( to bring up the formula

To complete the calculation you will need to have the following information:

Values- This is a list of all of the cash flows including the initial inflow or outflow. The list
of values must include at least one positive and one negative value.

Example: Let’s use the same example from the NPV section. You can invest $100 now with
expected future annual cash flows of $10, $25, $40, and $50 in years 1-4. Enter -100, 10,
25, 40, 50 in cells A1:A5. Enter your formula as =IRR(A1:A5). You should get an answer of
7.71%. Similarly, you can indicate the cash flows regardless of which row in which they
have been entered.

5
FINANCIAL CALCULATORS (HP10BII AS AN EXAMPLE)

Important Stuff

1. Make sure there are at least two decimals, ideally more. Otherwise rounding
off may create problems in a multi-step problem. (See item (3) below.)
2. Always enter the interest rate values in percentage form, not decimals. So, if
the interest rate is 10%, enter 10. This is different from spreadsheets.
3. Make sure you understand how to access all functions, especially the yellow
ones. Press the yellow key just above the C key to access the yellow function
you want. For example, it is good to CLEAR your calculator’s memory after
you do a calculation. So, press yellow key followed by CALL (in yellow).
Similarly, press the yellow key then DISP in yellow and then 8; that will give
you 8 decimals.
4. Make sure you get comfortable with the financial functions as early as
possible. (Some are in yellow.)

PV, FV, N: These functions are obvious, where N is the number of periods.
I/YR: The interest rate function is a bit complicated. First, as indicated above, you enter
the number in % form. But the calculator unfortunately assumes that every problem has
annual periods with monthly compounding. To fix this, enter 1, followed by yellow key,
followed by P/YR (in yellow). Now you can enter the interest rate that matches your
problem’s periodicity and the corresponding N. So if you have 10 years with annual
compounding and an annual interest of 12% you will enter I/YR as 12 and N as 10. If
compounding is monthly, you will enter 1 as I/YR and 120 for N.

NPV and IRR: With changing cash flows you need to use the CF j function. Suppose you
want to calculate the NPV and IRR of the same problem we did earlier: -100, 10, 25, 40,
50. You will enter -100 followed by CFj, then 10 followed by CFj,…and finally yellow key
followed by IRR (it is in yellow) to get 7.71%, and then yellow key followed by NPV to get
-$6.04!