Archives for September 2020

The following discussion is intended as a companion to my book Corporate Finance Made Simple: Corporate Finance Explained in 100 Pages or Less.

Click any of the links below to jump to the applicable part of this page.

• Future value
• Present value
• Net present value (NPV)
• Internal rate of return (IRR)
• Excel’s XIRR function
• Yield to maturity (YTM)

Future Value

The “future value” of an asset is the amount that the asset will be worth at some specific point in the future, given a particular rate of return.

Example: We want to calculate the future value of a 7-year CD with an initial deposit of $1,000 and a 6% rate of return compounded annually.

We calculate present value as follows:

FV = PV x (1 + r)ⁿ

where:

• FV is future value,
• PV is present value,
• r is the rate of return (as a decimal), and
• n is the number of periods.

So you could calculate the future value manually by typing the following equation into a given cell:

= 1000*1.06^7

If you’ve done everything correctly, Excel should provide the result: $1,503.63.

Or you could enter the appropriate input values in cells in Excel, such as in the image below. (The text in column B isn’t necessary for the formula. Its purpose is simply to make it easier for you to remember what the number in each cell refers to.)

Then you could enter the following in a new cell, thereby calculating the FV by referencing your input cells rather than manually entering the values in your equation.

= A1*(1+A2)^A3

The above approach is often convenient, because it makes it easy to see how the result changes as you adjust one or more of the inputs. For instance if you change the cell that contains your rate of return from 6% to any other number, your calculated future value will immediately change accordingly.

Alternatively, you could use Excel’s built-in “FV” function, which requires the following parameters:

• Rate (the rate of return)
• Nper (the number of periods)
• Pmt (the periodic payment)
• PV (present value)
• Type (either a zero or a one, to indicate the timing of any periodic payment; 1 indicates payment at the beginning of the period; 0 or leaving the field blank indicates payment at the end of the period)

You can access the FV function by clicking Insert -> Function -> FV. Then in your formula builder window, enter the following:

• Rate = A2
• Nper = A3
• Pmt = 0 (We’re dealing with a CD for which there are no periodic payments at all.)
• PV = -A1 (We enter a negative value here, because in Excel’s financial functions, a negative value indicates a cash outflow. We’re paying $1,000 to buy the CD in the first place, so we need a negative value in order to represent this outflow.)
• Type = left blank

Click “done,” and you should see the appropriate future value ($1,503.63) appear in the cell.

If you click on the cell and look in your formula bar, you should see the following formula:

=FV(A2,A3,0,-A1)

Of note: If you prefer, you can enter the applicable values directly into the formula builder window rather than entering them by reference to cells. (For example, instead of entering A2 for the rate, you could enter 0.06.)

Present Value

The “present value” of a future cash flow is the amount that that future cash flow is worth today.

Example: How much would you be willing to pay for an investment that will pay you $10,000 five years from now, if you could earn a 9% rate of return per year, via other investments with similar risk? (That is, what is the present value of a $10,000 cash flow five years in the future, with a 9% discount rate?)

We calculate present value as follows:

PV = FV / (1 + r)ⁿ

where again:

• PV is present value,
• FV is future value,
• r is the discount rate (as a decimal), and
• n is the number of periods.

So you could calculate the present value manually by typing the following equation into a given cell:

= 10000/1.09^5

If you’ve entered your equation correctly, Excel should provide the result: $6,499.31.

Or you could enter the appropriate input values in cells in Excel, such as in the image below.

Then you could enter the following equation in another cell, thereby calculating the present value by referencing your input cells:

= A1/(1+A2)^A3

Or you could use Excel’s built-in “PV” function, which requires the following parameters:

• Rate
• Nper
• Pmt
• FV
• Type (again, either a zero or a one; 1 indicates payment at the beginning of the period; 0 or leaving the field blank indicates payment at the end of the period)

You can access the PV function by clicking Insert -> Function -> PV. Then in your formula builder window, enter the following:

• Rate = A2
• Nper = A3
• Pmt = 0 (In our example there are no periodic payments at all.)
• FV = A1
• Type = left blank (Again, there are no periodic payments, so the timing of the nonexistent payments doesn’t matter. We could enter a one, a zero, or leave it blank, and we’d get the same answer regardless.)

Click “done,” and if you’ve done everything correctly, you should see the present value (-$6,499.31) appear in the cell. (Note that it’s a negative value, simply to indicate a cash outflow again. That is, you’d have to pay $6,499.31 today, in order to have $10,000 after five years, given a 9% rate of return.)

If you click on the cell and look in your formula bar, you should see the following formula:

=PV(A2,A3,0,A1)

Again, if you prefer, you can enter the applicable values directly into the formula builder window rather than entering them by reference to cells. (For example, instead of entering A2 for the rate, you could enter 0.06.)

Net Present Value (NPV)

The “net present value” of an investment is the sum of the present values of each of the cash inflows, minus the sum of the present values of each of the cash outflows.

Example: We want to calculate the NPV for a project with an initial cost of $1,000, which is projected to provide the following annual cash inflows, given a 7% discount rate.

• End of year 1: $200,
• End of year 2: $300,
• End of year 3: $400,
• End of year 4: $500.

We could manually calculate the net present value, by calculating the present value of each individual cash flow, then summing those present values. But that would be inconvenient, to say the least. (And with more complex streams of cash flows, it becomes thoroughly impractical.)

A much faster approach is to use Excel’s “NPV” function. The NPV function requires the following parameters:

• Rate (the discount rate)
• Value1 (the future value of the first cash flow)
• Value2 (the future value of the second cash flow)
• etc

We begin by entering our relevant inputs:

Then we choose Insert -> Function -> NPV.

An important point about the NPV function is that it assumes that cash flows happen at the end of each period. So if you have any cash flow that occurs at the beginning of the first period, you have to exclude it when using the NPV function, then manually add/subtract the initial cash flow in question. So in our example, we will not be including the value in cell A2 in our NPV function. Rather, we’ll calculate the NPV without it, then we will manually subtract $1,000 from the calculated NPV to arrive at the actual NPV for our investment.

In the formula builder window, you can manually enter A3 for Value1, A4 for Value2, A5 for Value3, and so on. A faster approach, however, is to enter a range of cells for Value1. That is, for Value1 you would select (highlight) the entire range from A3 to A6. Or you could type A3:A6 as the value for Value1.

When you click “done,” the NPV for the cash inflows should appear: $1,156.91. But remember, that does not account for the present value of the cash flow at the beginning of the first period. We have to subtract that manually. When we do so, we see that the NPV for the investment is $156.91.

Internal Rate of Return (IRR)

The “internal rate of return” (IRR) for an investment is the discount rate at which net present value of the cash flows is zero (i.e., it is the discount rate at which the present value of the cash outflows is equal to the present value of the cash inflows).

Example: We want to calculate the IRR for a project with an initial cost of $1,000, which is projected to provide the following annual cash flows:

• End of year 1: $200,
• End of year 2: $300,
• End of year 3: $400,
• End of year 4: $500.

There’s no practical way to calculate IRR manually, as the only way to do it is by trial and error. Fortunately, Excel can do the math instantly via the IRR function, which takes in the following parameters:

• Values: (the range of cells that contain the cash flows in question)
• Guess: (your guess as to the IRR — basically you’re telling Excel where to begin the trial and error process. If you leave this blank, Excel will start with 0.1 (i.e., 10 percent)).

Your first step would be to enter each of the cash flows, including the initial outflow (which, again, must be negative to indicate that it’s an outflow). Like so:

Then you can select another cell (wherever you want the IRR to appear) and click Insert -> Function -> IRR. Then in your formula builder window, provide the following inputs:

• Values = A1:A5
• Guess = left blank

When you click “done,” Excel should give you the calculated IRR (in this case, 12.8%, which may appear as 13% depending on how many decimals you currently have it set to show).

If you click on the cell and look in your formula bar, you should see the following formula:

=IRR(A1:A5)

Excel’s XIRR Function

One limitation of Excel’s IRR function is that it only works if the cash flows occur at regular intervals (e.g., monthly or annually).

If you have a series of cash flows in which there is no regular interval, you can use Excel’s XIRR function to calculate the internal rate of return for the series.

The XIRR function requires the following parameters:

• Values (the dollar amounts of the cash flows in question)
• Dates (a range of cells with the dates on which the cash flows will occur)
• Guess (your guess as to the IRR)

Example: We want to know the IRR for the following series of cash flows:

• $5,000 outflow on 1/1/2021
• $1,000 inflow on 10/15/2021
• $1,500 inflow on 3/10/2022
• $3,000 inflow on 8/20/2022
• $2,000 inflow on 6/15/2023

You would begin by typing the relevant inputs into cells, like so:

Then you can select another cell (wherever you want the IRR to appear) and click Insert -> Function -> XIRR. Then in your formula builder window, provide the following inputs:

• Values = A1:A5
• Dates = B1:B5
• Guess = left blank

When you click “done,” Excel should give you the calculated IRR (in this case, 28.6%).

If you click on the cell and look in your formula bar, you should see the following formula:

=XIRR(A1:A5,B1:B5)

Yield to Maturity (YTM)

Yield to maturity (YTM) is the rate of return that a bond buyer would earn if he/she buys the bond at today’s price, holds the bond to maturity, and receives all the promised payments on time (i.e., there is no default).

Example #1: A three-year bond has a $1,000 par value and a 6% coupon rate, with interest paid annually ($60 every 12 months). The bond is currently selling for $960. What is the bond’s YTM?

To find the YTM, we can use Excel’s “Rate” function. The Rate function requires the following parameters:

• Nper (the number of periods)
• Pmt (the periodic payment)
• PV (present value)
• FV (future value — in this case, the face value of the bond)
• Type (either a zero or a one, to indicate the timing of any periodic payment; 1 indicates payment at the beginning of the period; 0 or leaving the field blank indicates payment at the end of the period)
• Guess (your guess as to the YTM. If you leave this blank, Excel will start with 0.1 (i.e., 10 percent))

We could provide our inputs in a spreadsheet as follows:

Then you would select another cell (wherever you want the YTM to appear) and click Insert -> Function -> Rate. Then in your formula builder window, provide the following inputs:

• Nper = A2
• Pmt = A3
• PV = -A4 (Again, we use a negative value here to reflect the fact that purchasing the bond would reflect a cash outflow.)
• FV = A1
• Type (zero or left blank to indicate that coupons are paid at the end of each period)
• Guess (left blank)

When you click “done,” Excel should give you the calculated YTM (in this case, 7.54%).

Things are slightly more complicated in a case in which interest payments are made more often than annually. And this is important, because most bonds in the US pay interest semiannually — every six months. When a bond pays interest more often than once per year, the quoted YTM represents the actual rate per payment period, multiplied by the number of payment periods per year.

Example #2: A three-year bond has a $1,000 par value and a 6% coupon rate, with interest paid semiannually ($30 every 6 months). The bond is currently selling for $960. What is the bond’s YTM?

In this case we could provide our inputs as follows:

And in our formula builder window for the Rate function, we would provide the following:

• Nper = A2*2 (i.e., six semiannual periods)
• Pmt = A3
• PV = -A4 (Again, we use a negative value here to reflect the fact that purchasing the bond would reflect a cash outflow.)
• FV = A1
• Type (zero or left blank to indicate that coupons are paid at the end of each period)
• Guess (left blank)

When you click “done,” Excel should give you the calculated rate (in this case, 3.757%). But this is the semiannual rate. To annualize it, we would multiply this figure by two (to reflect that there are two semiannual periods per year). The result would be the bond’s YTM: 7.514%.