Value Of Money X Time

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.

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 PV is now $1,829.46.

Present