Excel at Financial-Analysis Calculations
By Donald J. Valachi, CCIM, CPA

Solving time-value-of-money problems commonly encountered in the financial analysis of commercial real estate investments can be accomplished quickly and accurately with the aid of computer spreadsheet software such as Microsoft Excel. Specifically, Excel can calculate discounted cash flow measures of value and return such as net present value, internal rate of return, and modified internal rate of return.

The following examples use Excel 2000, although earlier versions also work similarly. These examples are designed to facilitate the process of calculating NPV, IRR, and MIRR. In addition, unlike calculators, Excel allows users to print out their calculations. Familiarity with the process of compounding and discounting as well as basic spreadsheet calculations is assumed. (See "Excel at Basic Mortgage Calculations," CIRE, September/October 1998.)

Net Present Value
NPV is the difference between the present value of an investment's cash inflows and the present value of its cash outflows. The investor specifies a target rate of return for investing capital; it is an "opportunity cost" concept.

The general rule for considering the investment is if the NPV is greater than zero, the investment should be accepted; if the NPV is negative, it should be rejected. A positive NPV means the investor can expect to earn a rate of return greater than the required return rate for such an investment.

To calculate NPV, assume an investor makes a $100,000 investment today to receive the following annual after-tax cash inflows: $9,000 at the end of year one, $10,000 at the end of year two, $11,000 at the end of year three, ($3,000) at the end of year four, $12,000 at the end of year five, and $180,000 at the end of year six. The investor's required rate of return on equity is 12 percent. Enter the assumptions into a template. (The shaded cell, B11, is left blank; this is where the answer will appear.) The NPV of $19,933 in cell B11 is calculated as follows:

  1. Move the cursor to cell B11, where the answer will be displayed.
  2. Click on the Paste Function icon (fx). A box of available options appears. (If the box obscures your data, click on the title bar, drag the box out of the way, and release the mouse.)
  3. From the Function category menu, select Financial.
  4. From the Function name menu, select NPV.
  5. Click on the OK button to continue. A box appears to guide you through the calculation. (If the box obscures your data, click on the top of the box, drag the box out of the way, and release the mouse.)
  6. At the Rate prompt, click on cell B9, which specifies the cell containing the requested information.
  7. Press Tab to move to the next prompt.
  8. At the Value1 prompt, select cells B3:B8, which specifies the cells containing the requested information.
  9. Click on the OK button, which closes the box. The present value of the cash inflows of $119,933 is displayed in cell B11.
  10. To calculate NPV, type +B2 at the end of the NPV formula in the formula bar near the top of the screen and press Enter. The NPV of $19,933 is displayed in cell B11.
  11. Click on the Increase Decimal icon or Decrease Decimal icon to display additional or fewer decimal places.

    Net Present Value

A

B

1

Years

Cash flow

2

0

-100,000

3

1

9,000

4

2

10,000

5

3

11,000

6

4

-3,000

7

5

12,000

8

6

180,000

9

Discount rate

12%

10

11

NPV =

$19,933

Internal Rate of Return
IRR equates the present value of the cash inflows and the present value of the cash outflows. The decision rule for IRR is if the IRR is greater than or equal to the investor's required rate of return, the investment should be accepted; otherwise it should be rejected.

Using the same investment assumptions, what IRR is earned on the initial $100,000 investment? Start with the same template for the NPV problem, making changes as necessary. The yield of 16 percent in cell B10 is calculated as follows:

  1. Move the cursor to cell B10, where the answer will be displayed.
  2. Click on the Paste Function icon (fx). A box of available options appears. (If the box obscures your data, click on the title bar, drag the box out of the way, and release the mouse.)
  3. From the Function category menu, select Financial.
  4. From the Function name menu, select IRR.
  5. Click on the OK button to continue. A box appears to guide you through the calculation. (If the box obscures your data, click on the top of the box, drag the box out of the way, and release the mouse.)
  6. At the Values prompt, select cells B2:B8, which specifies the cells containing the requested information.
  7. Press Tab to move to the next prompt.
  8. Leave the Guess prompt blank. (In most cases you do not need to provide a guess for the IRR calculation. If the guess is omitted, it is assumed to be 10 percent.)
  9. Click on the OK button, which closes the box. The yield (IRR) of 16 percent is displayed in cell B10.
  10. Click on the Increase Decimal icon or Decrease Decimal icon to display additional or fewer decimal places.

    Internal Rate of Return

A

B

1

Years

Cash flow

2

0

-100,000

3

1

9,000

4

2

10,000

5

3

11,000

6

4

-3,000

7

5

12,000

8

6

180,000

9

 

10

IRR =

16%

Modified Internal Rate of Return
MIRR is an alternative to the traditional calculation of the IRR in that it computes an IRR with an explicit reinvestment rate assumption.

MIRR has several versions; the Excel version uses the following rates: Finance_rate is the interest rate used to discount all negative cash flows to the beginning of the holding period; Reinvest_rate is the rate used to compound all positive cash flows to the end of the holding period.

The discount rate that equates the present value of all negative cash flows (including the down payment) to the future or terminal value of all the positive cash flows is the MIRR.

To calculate, assume the same cash flow assumptions used in the previous examples. In addition, assume negative cash flows will be discounted at an interest rate of 6 percent and positive cash flows will be compounded at an interest rate of 10 percent.

What annual MIRR would be earned on the initial $100,000 investment? Enter the assumptions into the template. The MIRR of 15 percent in cell B12 is calculated as follows:

  1. Move the cursor to cell B12, where the answer will be displayed.
  2. Click on the Paste Function icon (fx). A box of available options appears. (If the box obscures your data, click on the title bar, drag the box out of the way, and release the mouse.)
  3. From the Function category menu, select Financial.
  4. From the Function name menu, select MIRR.
  5. Click on the OK button to continue. A box appears to guide you through the calculation. (If the box obscures your data, click on the top of the box, drag the box out of the way, and release the mouse.)
  6. At the Values prompt, select cells B2:B8, which specifies the cells containing the requested information.
  7. Press Tab to move to the next prompt.
  8. At the Finance_rate prompt, click on cell B9, which specifies the interest rate used to discount any negative cash flows to the beginning of the holding period.
  9. Press Tab to move to the next prompt.
  10. At the Reinvest_rate prompt, click on cell B10, which specifies the required rate of return, which is the interest rate received on the cash inflows that are reinvested for the duration of the project.
  11. Click on the OK button, which closes the box. The yield (MIRR) of 15 percent is displayed in cell B12.
  12. Click on the Increase Decimal icon or Decrease Decimal icon to display additional or fewer decimal places.


    Modified Internal Rate of Return

A

B

1

Years

Cash flow

2

0

-100,000

3

1

9,000

4

2

10,000

5

3

11,000

6

4

-3,000

7

5

12,000

8

6

180,000

9

Finance_rate

6%

10

Reinvest_rate

10%

11

 

12

MIRR =

15%

More to Explore
These examples illustrate the simplicity of using spreadsheet programs such as Excel to make a variety of basic time-value-of-money calculations. These examples do not, of course, demonstrate the full range of options and computing power available with the software.

Spreadsheet Software Can Help You Calculate Discounted Cash Flow Measures of Value and Return.
By Donald J. Valachi, CCIM, CPA

Close window