CORPORATE FINANCE

Chapter 4

			Ross, Westerfield, and Jordan's Excel Master
			Essentials of Corporate Finance, 9th edition
			by Brad Jordan and Joe Smolira
			Version 9.0
			Chapter 4
			In these spreadsheets, you will learn how to use the following Excel functions:
			The following conventions are used in these spreadsheets:
			1) Given data in blue
			2) Calculations in red
			NOTE: Some functions used in these spreadsheets may require that
			the "Analysis ToolPak" or "Solver Add-In" be installed in Excel.
			To install these, click on the File button
			then "Options," "Add-Ins" and select
			"Go." Check "Analysis ToolPak" and
			"Solver Add-In," then click "OK."

FV

/xl/drawings/drawing1.xml#'Section%204.1'!A126

PV

/xl/drawings/drawing1.xml#'Section%204.2'!A17

Two-way data tables

/xl/drawings/drawing1.xml#'Section%204.2'!A47

RATE

/xl/drawings/drawing1.xml#'Section%204.3'!A17

NPER

/xl/drawings/drawing1.xml#'Section%204.3'!A62

FVSCHEDULE

/xl/drawings/drawing1.xml#'Section%204.3'!A111

Section 4.1

	Chapter 4 - Section 1
	Future Value and Compounding
Excel contains numerous financial functions, many of which relate to the time value of money. We will begin by using equations before moving to Excel's functions.
Example 4.1: Interest on Interest
Suppose you have the following investment opportunity for two years:
	Interest rate per year:			14.0%
	Initial investment:			$ 325
	How much will you have at the end of the investment? How much is simple interest? How much is compound interest?
	At the end of one year, you will have:				$ 370.50
	At the end of the investment you will have:				$ 422.37
	The total interest earned is:				$ 97.37
	Interest on original investment per year:				$ 45.50
	Total simple interest:				$ 91.00
	Total compound interest:				$ 6.37
As shown in the textbook, the future value of $1 is found by the equation FV = $1 ´ (1 + r)t. Suppose we make the following investment:
	Initial investment:		$100
	Interest rate per year:		10%
What is the value of the investment each year over the next 5 years? How much of the interest is simple interest and how much is compound interest? We will answer this question with the following table:
	Year	Beginning Amount	Simple Interest	Compound Interest	Total Interest	Ending Amount	Amount with Simple Interest
	1	$ 100.00	$ 10.00	$ 0.00	$ 10.00	$ 110.00	$ 110.00
	2	110.00	10.00	1.00	11.00	121.00	120.00
	3	121.00	10.00	2.10	12.10	133.10	130.00
	4	133.10	10.00	3.31	13.31	146.41	140.00
	5	146.41	10.00	4.64	14.64	161.05	150.00
	Totals		$ 50.00	$ 11.05	$ 61.05
So what does simple interest look like compared to compound interest? We can use Excel to draw a graph for us. First we need to set up a table that shows the value with simple interest and the total compound interest.
	Year	Amount with Simple Interest	Total Compound Interest
	1	$ 110.00	$ - 0
	2	$ 120.00	$ 1.00
	3	$ 130.00	$ 3.10
	4	$ 140.00	$ 6.41
	5	$ 150.00	$ 11.05
Now we can graph the contribution of compounding to the future value of our investment.
To see the effect of compound interest, change the interest rate and see how the compound interest grows as the interest rate changes.
RWJ Excel Tip
To insert this bar chart, we highlighted the columns we wanted in the graph, went to the Insert tab, and then slected Column. We chose the 2-D Stacked Column option. To get the border shadowing effect, we right-clicked on the graph, selected Format Plot Area and chose the Shadow option.
In the past, future value tables were very common. Future value tables calculated the future interest factor for a variety of interest rates and time periods. We can construct a future value table relatively quickly in Excel. By the way, we will show you a much more efficient method in the next section.
	Number of	Interest Rate
	Periods	0%	5%	10%	15%	20%
	0	1.000	1.000	1.000	1.000	1.000
	1	1.000	1.050	1.100	1.150	1.200
	2	1.000	1.103	1.210	1.323	1.440
	3	1.000	1.158	1.331	1.521	1.728
	4	1.000	1.216	1.464	1.749	2.074
	5	1.000	1.276	1.611	2.011	2.488
	6	1.000	1.340	1.772	2.313	2.986
	7	1.000	1.407	1.949	2.660	3.583
	8	1.000	1.477	2.144	3.059	4.300
	9	1.000	1.551	2.358	3.518	5.160
	10	1.000	1.629	2.594	4.046	6.192
An important fact about compound interest is that it results in exponential growth. To see the exponential growth in practice, we can graph the future value table. It looks like this:
RWJ Excel Tip
There is a "bug" in Excel when graphing a table like the one above. If a table has text in the header row and column, Excel will automatically use the text in the legend of the graph. However, when the header row and column are numbers, Excel will not use the numbers in the legend, but rather include them in the graph. To include the numbers in the legend and on the vertical axis, try the following: First, select just the data in the data and ignore the header row and column. Next, right click on the entire chart and choose "Select Data." In the left hand column, highlight the data series you want to include a legend for (Series 1, Series 2, etc.,) then select "Edit." This brings up a box that allows you to choose the "Series Name." To include the number in the legend, simply select the cell that has the header you want to include. You will need to repeat this for every column in the table. To include the column with the number of years as the horizontal axis, go to the "Horizontal (Category) Axis Labels," select "Edit", then highlight the array that has the correct values for the horizontal axis.
Now that we have calculated the future value of a lump sum with the equation, we will use Excel's FV function to calculate the future value. Suppose you have the following investment opportunity:
	Interest rate per year:		12%
	Number of years:		3
	Initial investment:		$ 400
How much will you have at the end of the investment. Using the FV function, we find that you will have:
	Future value:		$ 561.97
RWJ Excel Tip
To use the FV function, we entered the following:
The Rate is simply the interest rate, Nper is the number if periods, and Pv is the present value. We left the payment and type blank for now, but we will discuss this in more detail later. Notice also that we put a negative sign in front of the present value. Excel works like a calculator in that it expects cash flows. If we had left the present value as a positive number we would have simply gotten a negative answer. Since we prefer our answers to show as positive, we entered a negative in front of the present value.
Example 4.3: How Much for That Island?
Consider Peter Minuit's purchase of Manhattan Island from the American Indians. Using the FV function, if the purchase price of the island was invested, how much would that investment be worth today?
	Purchase price:				$ 24
	Interest rate:				10%
	Number of years:				390
	Value today:		$ 333,701,726,356,917,950.00
RWJ Excel Tip
Two things about the above example. First, we did not want to change the column width for the entire spreadsheet to display the future value. To get the future value to display, we merged 3 cells by using the merge icon: In merging cells, you simply select the cells you want merged into one cell and click on the icon. The second thing is that if you notice, the future value has all zeroes in the last three digits of the dollar amount and in the cents. You might think this is strange, and indeed it is. The reason is that while Excel is very precise, it only calculates to 15 significant digits. Although this generally does not create a problem in most calculations, it is something that you should consider if you are using very large or very small numbers.
If for some reason you do need more accurate calculations, www.precisioncalc.com has an add-in to Excel available that will calculate to 32,767 digits.

Future Value, Simple Interest, and Compound Interest

Amount with simple interest 110 120 130 140 150 Compound interest 0 1.0000000000000284 3.1000000000000512 6.4100000000000819 11.051000000000101

Time (years)

Future Value ($)

Future Value of $1 for Different Periods and Rates

0% 0 1 2 3 4 5 6 7 8 9 10 1 1 1 1 1 1 1 1 1 1 1 5% 0 1 2 3 4 5 6 7 8 9 10 1 1.05 1.1025 1.1576250000000001 1.21550625 1.2762815625000001 1.340095640625 1.4071004226562502 1.4774554437890626 1.5513282159785158 1.6288946267774416 10% 0 1 2 3 4 5 6 7 8 9 10 1 1.1000000000000001 1.2100000000000002 1.3310000000000004 1.4641000000000004 1.6105100000000006 1.7715610000000008 1.9487171000000012 2.1435888100000011 2.3579476910000015 2.5937424601000019 15% 0 1 2 3 4 5 6 7 8 9 10 1 1.1499999999999999 1.3224999999999998 1.5208749999999995 1.7490062499999994 2.0113571874999994 2.3130607656249991 2.6600198804687483 3.0590228625390603 3.5178762919199191 4.0455577357079067 20% 0 1 2 3 4 5 6 7 8 9 10 1 1.2 1.44 1.728 2.0735999999999999 2.4883199999999999 2.9859839999999997 3.5831807999999996 4.2998169599999994 5.1597803519999994 6.1917364223999991 0% 0 1 2 3 4 5 6 7 8 9 10 1

Time (years)

Future value of $1

Section 4.2

	Chapter 4 - Section 2
	Present Value and Discounting
Now that we have used the FV function, we will skip entering an equation to find the present value, but rather talk about the PV function. The PV syntax is similar to the FV syntax.
Example 4.5: Saving Up
Suppose you want to buy a new car. How much do you have to invest today in order to buy the car in the future?
	Future value:		$ 68,500
	Number of periods:		2
	Interest rate:		9%
	Present value:		$ 57,655.08
RWJ Excel Tip
To use the PV function, we entered the following:
The Rate is simply the interest rate, Nper is the number of periods, and Fv is the future value. We left the payment and type blank for now, but we will discuss these later on. Notice also that we put a negative sign in front of the future value. Excel works like a calculator in that it expects cash flows. If we had left the future value as a positive number we would have simply gotten a negative answer. Since we prefer our answers to show as positive, we entered a negative in front of the future value.
Suppose we want to create a table with the present value factors for different interest rates and periods. A two-way data table allows us to do this very easily. First, we'll set up a basic PV problem, with $1 as the future value.
	Future value:		$ 1
	Number of periods:		2
	Interest rate:		9%
	Present value:		$ 0.8417
RWJ Excel Tip
To set up a two-way data table, first create the rows and columns for the table. Next, in the upper left hand corner, enter the equation you would like to use into the calculations in the cell. Next, select the cell with the equation you want to use in the data table, go to the Data tab, What-If Analysis, then Data Table. Excel will prompt you to enter the variables in your table that correspond to the row and column numbers you entered. For this data table , our entries were:
Notice that Excel made our choices absolute references by default. Just hit OK and the data table will be filled in automatically. We left the calculation in the upper left hand corner showing in this case, but remember we could hide this number by right-clicking, selecting Format Cells, choosing Custom, and entering the custom type as a semicolon.
		Interest Rate
		$ 0.8417	0%	5%	10%	15%	20%
	Number of Periods	0	1.00000	1.00000	1.00000	1.00000	1.00000
		1	1.00000	0.95238	0.90909	0.86957	0.83333
		2	1.00000	0.90703	0.82645	0.75614	0.69444
		3	1.00000	0.86384	0.75131	0.65752	0.57870
		4	1.00000	0.82270	0.68301	0.57175	0.48225
		5	1.00000	0.78353	0.62092	0.49718	0.40188
		6	1.00000	0.74622	0.56447	0.43233	0.33490
		7	1.00000	0.71068	0.51316	0.37594	0.27908
		8	1.00000	0.67684	0.46651	0.32690	0.23257
		9	1.00000	0.64461	0.42410	0.28426	0.19381
RWJ Excel Tip
We have the legend on the left hand side of the table running vertically. To do this, we merged the cells, typed in the text, right clicked on the cells and selected "Format Cells." Using the "Alignment" option, we moved the "Text" wheel to vertical.
Graphically, the present value factors look like this:

Present Value of $1 for Different Periods and Rates

0% 0 1 2 3 4 5 6 7 8 9 1 1 1 1 1 1 1 1 1 1 5% 0 1 2 3 4 5 6 7 8 9 1 0.95238095238095233 0.90702947845804982 0.86383759853147601 0.82270247479188197 0.78352616646845896 0.74621539663662761 0.71068133013012147 0.67683936202868722 0.64460891621779726 10% 0 1 2 3 4 5 6 7 8 9 1 0.90909090909090906 0.82644628099173545 0.751314 80090157754 0.68301345536507052 0.62092132305915493 0.56447393005377722 0.51315811823070645 0.46650738020973315 0.42409761837248466 15% 0 1 2 3 4 5 6 7 8 9 1 0.86956521739130443 0.7561436672967865 0.65751623243198831 0.57175324559303342 0.49717673529828987 0.43232759591155645 0.37593703992309269 0.32690177384616753 0.28426241204014574 20% 0 1 2 3 4 5 6 7 8 9 1 0.83333333333333337 0.69444444444444442 0.57870370370370372 0.48225308641975312 0.4018775720164609 0.33489797668038412 0.27908164723365342 0.23256803936137788 0.1938066994678149

Time (years)

Present value of $1

Section 4.3

	Chapter 4 - Section 3
	More on Present and Future Values
Finding the interest rate necessary for a present value to reach a desired future value in a desired time period is a relatively simple problem using Excel.
Example 4.10: Saving for College
You are trying to determine the interest rate you will need to earn on your lump sum investment in order to be able to send your child to college. You have the following information and want to determine what interest rate you will need to achieve your goal.
	Present value:		$ 35,000
	Future value:		$ 80,000
	Number of periods:		8
	Interest rate:		10.89%
RWJ Excel Tip
To find the interest rate, we used the RATE function and entered the following:
NPER is the number of periods, Pv is the present value, and Fv is the present value. We left the payment and type blank for now, but we will discuss these later on. Notice also that we put a negative sign in front of the present value. Excel works like a calculator in that it expects cash flows. We could have also made the present value positive and the future value negative.
Example 4.11: Only 18,262.5 Days to Retirement
You want to retire as a millionaire. You know how much you have to invest today and the number of years until retirement. What interest rate must you earn to achieve your goal.
	Present value:		$ 10,000
	Future value:		$ 1,000,000
	Number of periods:		50
	Interest rate:		9.65%
In other problems, we may know the present value and future value, along with the appropriate interest rate. In the next case, we want to know how many periods it takes to reach our goal. Again, Excel gives us a simple method to answer this question.
Example 4.12: Waiting for Godot
You are saving up to buy the Godot Company and have the following information. How long must you wait to buy the company?
	Present value:		$ 2,300,000
	Future value:		$ 10,000,000
	Interest rate:		5%
	Number of periods:		30.12
RWJ Excel Tip
To find the number of periods, we used the NPER function and entered the following:
Rate is the interest rate, Pv is the present value, and Fv is the future value. We left the payment and type blank for now, but we will discuss these later on. Notice also that we put a negative sign in front of the present value. Excel works like a calculator in that it expects cash flows. We could have also made the present value positive and the future value negative.
Future Value with Changing Interest Rates
Suppose you are going to make a lump sum deposit today, and the interest rate you will receive will change every year. With the following assumptions, how much will you have in 6 years?
	Present value:	$ 100
	Year	Interest rate
	1	8%
	2	6%
	3	10%
	4	15%
	5	11%
	6	9%
One way to calculate the future value is to compound the value each year. In year 1, we will receive the year 1 interest rate. We will use the value at the end of year 1 to calculate the future value in year 2 at the year 2 interest rate, and so on. Doing this, we find that the value each year is:
	Year	Value at year end
	1	$ 108.00
	2	114.48
	3	125.93
	4	144.82
	5	160.75
	6	175.21
While this process is more repetitive than difficult, Excel has a function that will calculate the future value of this amount. Using the FVSCHEDULE function, we find that the future value is:
	Future value:	$ 175.21
RWJ Excel Tip
To use the FVSCHEDULE, we entered:
In this function, Principal is the beginning deposit and Schedule is an array that contains the interest rates for each period.

Master it!

	Chapter 4 - Master it!
	Before the advent of financial calculators (and Excel), tables were often used in the calculation of present values and future values. Using a two-way data table, create a future value table and a present value table. To make the table a little more interesting, make sure the table will calculate the future values and present values for different dollar amounts. One thing we should note here is that you will not be able to simply copy and paste the tables presented earlier in this workbook. When a data table is created, you cannot insert or delete rows or columns at a later point in time.

Solution

	Master it! Solution
a.	Construct the following future value table:
	Present value:		$ 1
	Interest rate:		5%
	Number of periods:		7
	Future value:
			1%	2%	3%	4%	5%	6%	7%	8%	9%	10%	12%	14%	15%	16%	18%	20%	24%	28%	32%	36%
		1
		2
		3
		4
		5
		6
		7
		8
		9
		10
		11
		12
		13
		14
		15
		16
		17
		18
		19
		20
		21
		22
		23
		24
		25
		30
		40
		50
b.	Construct the following present value table:
	Future value:		$ 1
	Interest rate:		5%
	Number of periods:		7
	Present value:
			1%	2%	3%	4%	5%	6%	7%	8%	9%	10%	12%	14%	15%	16%	18%	20%	24%	28%	32%	36%
		1
		2
		3
		4
		5
		6
		7
		8
		9
		10
		11
		12
		13
		14
		15
		16
		17
		18
		19
		20
		21
		22
		23
		24
		25
		30
		40
		50