Excel

© 2022 Walden University, LLC Page 1 of 3

How to Calculate NPV and IRR Using the Present Value of an Annuity Table to Calculate NPV and IRR

When an organization’s leadership is looking to invest in a project, they need to know the project’s profitability potential. This is measured with net present value (NPV) and the internal rate of return (IRR). In many organizations, the accounting department is responsible for assembling their reports; however, a manager will be tasked with understanding the information gathered in those reports and making decisions based on that information. For this reason, it is crucial to have a solid understanding of accounting terms and concepts to effectively communicate to the financial team. Scenario: For this demonstration, refer to the Present Value of an Annuity, Receivable or Payable table (Table 1), which shows the year-end value of an annuity for years 1–20 with interest rates from 1% to 10%. Use this table to calculate net present value (NPV) using the steps that follow. Table 1. Present Value of an Annuity, Receivable or Payable (Year-End Values) 1%–10%

Periods

(n) Interest rates (r)

1% 2% 3% 4% 5% 6% 7% 8% 9% 10%

1 0.990 0.980 0.971 0.962 0.952 0.943 0.935 0.926 0.917 0.909 2 1.970 1.942 1.913 1.886 1.859 1.833 1.808 1.783 1.759 1.736 3 2.941 2.884 2.829 2.775 2.723 2.673 2.624 2.577 2.531 2.487 4 3.902 3.808 3.717 3.630 3.546 3.465 3.387 3.312 3.240 3.170 5 4.853 4.713 4.580 4.452 4.329 4.212 4.100 3.993 3.890 3.791

6 5.795 5.601 5.417 5.242 5.076 4.917 4.767 4.623 4.486 4.355

7 6.728 6.472 6.230 6.002 5.786 5.582 5.389 5.206 5.033 4.868 8 7.652 7.325 7.020 6.733 6.463 6.210 5.971 5.747 5.535 5.335 9 8.566 8.162 7.786 7.435 7.108 6.802 6.515 6.247 5.995 5.759 10 9.471 8.983 8.530 8.111 7.722 7.360 7.024 6.710 6.418 6.145

11 10.368 9.787 9.253 8.760 8.306 7.887 7.499 7.139 6.805 6.495

12 11.255 10.575 9.954 9.385 8.863 8.384 7.943 7.536 7.161 6.814 13 12.134 11.348 10.635 9.986 9.394 8.853 8.358 7.904 7.487 7.103 14 13.004 12.106 11.296 10.563 9.899 9.295 8.745 8.244 7.786 7.367 15 13.865 12.849 11.938 11.118 10.380 9.712 9.108 8.559 8.061 7.606

16 14.718 13.578 12.561 11.652 10.838 10.106 9.447 8.851 8.313 7.824

17 15.562 14.292 13.166 12.166 11.274 10.477 9.763 9.122 8.544 8.022 18 16.398 14.992 13.754 12.659 11.690 10.828 10.059 9.372 8.756 8.201 19 17.226 15.679 14.324 13.134 12.085 11.158 10.336 9.604 8.950 8.365 20 18.046 16.351 14.878 13.590 12.462 11.470 10.594 9.818 9.129 8.514

First, calculate the net present value (NPV): Step 1: Gather the following information to calculate the net present value:

• Initial Cost (Outlay for Investment)

• Net Annual Cash Flow

• Factor (PV of annual cash flows discounted at interest rate)

• Interest Rate (Discount Rate)

• Useful Life (Period)

Step 2: Apply the following NPV formula for finding the Factor: NPV = Initial Cost + (Net Annual Cash Flow × Factor)

© 2022 Walden University, LLC Page 2 of 3

Step 3: Use the Annuity Table (Table 1) to find the intersection of the Cost of Capital (9%) and the Useful Life (20 years). This intersection is the Factor (9.129). Step 4: Input the known values into the formula using the following information:

• Initial Cost (Outlay for Investment) = −5,000,000

• Net Annual Cash Flow = 820,000

• Factor = 9.129 Step 5: Plug the known values into the NPV formula: NPV = Initial Cost (−5,000,000) + (Net Annual Cash Flow of 820,000 × Factor of 9.129) NPV = −5,000,000 + (820,000 × 9.129)

NPV = −5,000,000 + 7,485,780 NPV = 2,485,780 Step 6: Using the Annuity Table (Table 1) and the NPV formula, you can now observe that the Net Present Value for an investment with a Useful Life of 20 years at an annual Interest Rate of 9% and a Factor of 9.129 is $2,485,780. Now, calculate the Internal Rate of Return (IRR) Scenario: For this demonstration, refer to the Present Value of an Annuity, Receivable or Payable table (Table 2), which shows the year-end value of an annuity for years 1–20 with interest rates from 11% to 20%. Use this table to calculate Internal Rate of Return (IRR) using the steps that follow. Table 2. Present Value of an Annuity, Receivable or Payable (Year-End Values) 11%–20%

Periods

(n) Interest rates (r)

11% 12% 13% 14% 15% 16% 17% 18% 19% 20%

1 0.901 0.893 0.885 0.877 0.870 0.862 0.855 0.847 0.840 0.833 2 1.713 1.690 1.668 1.647 1.626 1.605 1.585 1.566 1.547 1.528 3 2.444 2.402 2.361 2.322 2.283 2.246 2.210 2.174 2.140 2.106 4 3.102 3.037 2.974 2.914 2.855 2.798 2.743 2.690 2.639 2.589 5 3.696 3.605 3.517 3.433 3.352 3.274 3.199 3.127 3.058 2.991

6 4.231 4.111 3.998 3.889 3.784 3.685 3.589 3.498 3.410 3.326

7 4.712 4.564 4.423 4.288 4.160 4.039 3.922 3.812 3.706 3.605 8 5.146 4.968 4.799 4.639 4.487 4.344 4.207 4.078 3.954 3.837 9 5.537 5.328 5.132 4.946 4.772 4.607 4.451 4.303 4.163 4.031 10 5.889 5.650 5.426 5.216 5.019 4.833 4.659 4.494 4.339 4.192

11 6.207 5.938 5.687 5.453 5.234 5.029 4.836 4.656 4.486 4.327

12 6.492 6.194 5.918 5.660 5.421 5.197 4.988 7.793 4.611 4.439 13 6.750 6.424 6.122 5.842 5.583 5.342 5.118 4.910 4.715 4.533 14 6.982 6.628 6.302 6.002 5.724 5.468 5.229 5.008 4.802 4.611 15 7.191 6.811 6.462 6.142 5.847 5.575 5.324 5.092 4.876 4.675

16 7.379 6.974 6.604 6.265 5.954 5.668 5.405 5.162 4.938 4.730

17 7.549 7.120 6.729 6.373 6.047 5.749 5.475 5.222 4.990 4.775 18 7.702 7.250 6.840 6.467 6.128 5.818 5.534 5.273 5.033 4.812 19 7.839 7.366 6.938 6.550 6.198 5.877 5.584 5.316 5.070 4.843 20 7.963 7.469 7.025 6.623 6.259 5.929 5.628 5.353 5.101 4.870

© 2022 Walden University, LLC Page 3 of 3

Remember: IRR calculations rely on the same formula as NPV does, although the IRR is not the actual dollar value of the project. Instead, it is the annual return that makes the NPV equal to zero. Step 1: Gather the following information to calculate the IRR using the Annuity Table (Table 1):

• Initial Cost (Investment)

• Net Annual Cash Flow

• Factor (PV of annual cash flows discounted at interest rate)

• Useful Life (Period) Step 2: Input the known values using the following information:

• Initial Cost (Outlay for Investment) = −5,000,000

• Net Annual Cash Flow = 820,000

• Factor = Unknown

• Useful Life (Period) = 20 years

Step 4: Divide the Initial Cost (Investment) by the Net Annual Cash Flow to get an Approximate Factor.

• Initial Cost (−5,000,000) / Net Annual Cash Flow (820,000) = Approximate Factor (−6.097)

Step 5: Find the years of service (Periods) row that matches the Useful Life (Period) of 20 years, and then follow this line across until you find an Approximate Factor close to −6.097. The factor closest to −6.097 equals −5.929. Step 6: Scan up the Useful Life of 20 years from the Factor of 5.929 to find the Interest Rate of 16%. This is the IRR.

© 2022 Walden University, LLC Page 1 of 2

Net Present Value, Accounting Rate of Return, Internal Rate of Return, and Payback to Make Investment Decisions

One decision that managers make that affects the future pertains to evaluating capital investment opportunities. There will be outflows of cash in certain periods and inflows in others. It is not as simple as adding up all the inflows and subtracting the outflows since they occur at different times. For example, if the company invests $10,000 today and will receive $10,500 in 5 years, there is a positive difference of $500. Does this mean the investment should be made? A decision cannot be made based on this information alone because a dollar today is worth more than a dollar received in the future. One method that adjusts for this timing difference is discounted cash flow (DCF) analysis, which is also called net present value (NPV). This approach has four steps. First, identify the amount and timing of each cash flow; next, determine the discount rate; then, calculate the present value of each cash flow; and finally, calculate the NPV of the project (Davis & Davis, 2017). The discount rate is determined by the organization and is based on the risk of the project and the cost of capital. Different projects may have different discount rates. The present value of the investment does not have to be calculated, as it is invested at time zero. The present value of the future cash flows will need to be determined. The easiest method is to use a spreadsheet program such as Microsoft Excel. The investment is subtracted from the present value of the future cash flows. If the answer is positive, the project should be considered. If it is zero or negative, it probably should not be considered. Of course, there is no way to know precisely what the future cash flows will be. NPV is based on an educated estimate of these values. DCF is not the only method used to evaluate investment projects. Other methods include payback, accounting rate of return (ARR), and internal rate of return (IRR). They all can be useful in decision making, but it is important to also understand their drawbacks. The payback period is simply the time it takes for the cash inflows to equal the initial investment. If cash inflows are equal, the payback period is the net initial investment divided by the annual cash flow. It is expressed in years. If the cash flows are not equal, the annual cash flows are added together until they equal the net initial investment. The number of years, including partial years, is then computed. Each organization will have determined the number of years to payback that is acceptable. This is a quick screening tool that can be used prior to using more complicated methods. The limitations of this method are that it ignores the time value of money and the cash flows that occur after the payback period (Davis & Davis, 2017). The accounting rate of return is another method that can be used to evaluate investments. It is also called the return on investment (ROI). The formula is average

© 2022 Walden University, LLC Page 2 of 2

annual income from the project divided by the average annual investment in the project. It is easy to calculate, but it can lead to an incorrect decision, as it ignores the time value of money (Zimmerman, 2020). The internal rate of return (IRR) is another method that can be used when considering an investment. If the project’s IRR exceeds the organization’s cost of capital, the project should be undertaken. The method considers the time value of money; therefore, it is more accurate than those methods that do not consider this aspect. It is difficult and time consuming unless a spreadsheet program is used. If there are multiple changes in the cash flows from inflow to outflows, et cetera, the project could yield more than one IRR (Shim et al., 2012). When analyzing the feasibility of various alternative projects, consider all the quantitative methods given above, but also look at qualitative factors such as location, cultural differences, employee training needs, regulations, etc. In summary, capital budgeting decisions should not be made in a vacuum. Gather the metrics, the qualitative research, and talk to your team. Consider your options carefully and make the best decision you can make after considering all available information. References: Davis, C. E., & Davis, E. (2017). Managerial accounting (3rd ed.). Wiley.

Shim, J. K, Siegel, J. G., & Shim, A. I. (2012). Budgeting basics and beyond (4th ed.).

Wiley.

Zimmerman, J. L. (2020). Accounting for decision making and control (10th ed.).

McGraw-Hill/Irwin.

Template

© 2022 Walden University, LLC Page 1 of 3

How to Calculate NPV and IRR Using the Present Value of an Annuity Table to Calculate NPV and IRR

When an organization’s leadership is looking to invest in a project, they need to know the project’s profitability potential. This is measured with net present value (NPV) and the internal rate of return (IRR). In many organizations, the accounting department is responsible for assembling their reports; however, a manager will be tasked with understanding the information gathered in those reports and making decisions based on that information. For this reason, it is crucial to have a solid understanding of accounting terms and concepts to effectively communicate to the financial team. Scenario: For this demonstration, refer to the Present Value of an Annuity, Receivable or Payable table (Table 1), which shows the year-end value of an annuity for years 1–20 with interest rates from 1% to 10%. Use this table to calculate net present value (NPV) using the steps that follow. Table 1. Present Value of an Annuity, Receivable or Payable (Year-End Values) 1%–10%

Periods

(n) Interest rates (r)

1% 2% 3% 4% 5% 6% 7% 8% 9% 10%

1 0.990 0.980 0.971 0.962 0.952 0.943 0.935 0.926 0.917 0.909 2 1.970 1.942 1.913 1.886 1.859 1.833 1.808 1.783 1.759 1.736 3 2.941 2.884 2.829 2.775 2.723 2.673 2.624 2.577 2.531 2.487 4 3.902 3.808 3.717 3.630 3.546 3.465 3.387 3.312 3.240 3.170 5 4.853 4.713 4.580 4.452 4.329 4.212 4.100 3.993 3.890 3.791

6 5.795 5.601 5.417 5.242 5.076 4.917 4.767 4.623 4.486 4.355

7 6.728 6.472 6.230 6.002 5.786 5.582 5.389 5.206 5.033 4.868 8 7.652 7.325 7.020 6.733 6.463 6.210 5.971 5.747 5.535 5.335 9 8.566 8.162 7.786 7.435 7.108 6.802 6.515 6.247 5.995 5.759 10 9.471 8.983 8.530 8.111 7.722 7.360 7.024 6.710 6.418 6.145

11 10.368 9.787 9.253 8.760 8.306 7.887 7.499 7.139 6.805 6.495

12 11.255 10.575 9.954 9.385 8.863 8.384 7.943 7.536 7.161 6.814 13 12.134 11.348 10.635 9.986 9.394 8.853 8.358 7.904 7.487 7.103 14 13.004 12.106 11.296 10.563 9.899 9.295 8.745 8.244 7.786 7.367 15 13.865 12.849 11.938 11.118 10.380 9.712 9.108 8.559 8.061 7.606

16 14.718 13.578 12.561 11.652 10.838 10.106 9.447 8.851 8.313 7.824

17 15.562 14.292 13.166 12.166 11.274 10.477 9.763 9.122 8.544 8.022 18 16.398 14.992 13.754 12.659 11.690 10.828 10.059 9.372 8.756 8.201 19 17.226 15.679 14.324 13.134 12.085 11.158 10.336 9.604 8.950 8.365 20 18.046 16.351 14.878 13.590 12.462 11.470 10.594 9.818 9.129 8.514

First, calculate the net present value (NPV): Step 1: Gather the following information to calculate the net present value:

• Initial Cost (Outlay for Investment)

• Net Annual Cash Flow

• Factor (PV of annual cash flows discounted at interest rate)

• Interest Rate (Discount Rate)

• Useful Life (Period)

Step 2: Apply the following NPV formula for finding the Factor: NPV = Initial Cost + (Net Annual Cash Flow × Factor)

© 2022 Walden University, LLC Page 2 of 3

Step 3: Use the Annuity Table (Table 1) to find the intersection of the Cost of Capital (9%) and the Useful Life (20 years). This intersection is the Factor (9.129). Step 4: Input the known values into the formula using the following information:

• Initial Cost (Outlay for Investment) = −5,000,000

• Net Annual Cash Flow = 820,000

• Factor = 9.129 Step 5: Plug the known values into the NPV formula: NPV = Initial Cost (−5,000,000) + (Net Annual Cash Flow of 820,000 × Factor of 9.129) NPV = −5,000,000 + (820,000 × 9.129)

NPV = −5,000,000 + 7,485,780 NPV = 2,485,780 Step 6: Using the Annuity Table (Table 1) and the NPV formula, you can now observe that the Net Present Value for an investment with a Useful Life of 20 years at an annual Interest Rate of 9% and a Factor of 9.129 is $2,485,780. Now, calculate the Internal Rate of Return (IRR) Scenario: For this demonstration, refer to the Present Value of an Annuity, Receivable or Payable table (Table 2), which shows the year-end value of an annuity for years 1–20 with interest rates from 11% to 20%. Use this table to calculate Internal Rate of Return (IRR) using the steps that follow. Table 2. Present Value of an Annuity, Receivable or Payable (Year-End Values) 11%–20%

Periods

(n) Interest rates (r)

11% 12% 13% 14% 15% 16% 17% 18% 19% 20%

1 0.901 0.893 0.885 0.877 0.870 0.862 0.855 0.847 0.840 0.833 2 1.713 1.690 1.668 1.647 1.626 1.605 1.585 1.566 1.547 1.528 3 2.444 2.402 2.361 2.322 2.283 2.246 2.210 2.174 2.140 2.106 4 3.102 3.037 2.974 2.914 2.855 2.798 2.743 2.690 2.639 2.589 5 3.696 3.605 3.517 3.433 3.352 3.274 3.199 3.127 3.058 2.991

6 4.231 4.111 3.998 3.889 3.784 3.685 3.589 3.498 3.410 3.326

7 4.712 4.564 4.423 4.288 4.160 4.039 3.922 3.812 3.706 3.605 8 5.146 4.968 4.799 4.639 4.487 4.344 4.207 4.078 3.954 3.837 9 5.537 5.328 5.132 4.946 4.772 4.607 4.451 4.303 4.163 4.031 10 5.889 5.650 5.426 5.216 5.019 4.833 4.659 4.494 4.339 4.192

11 6.207 5.938 5.687 5.453 5.234 5.029 4.836 4.656 4.486 4.327

12 6.492 6.194 5.918 5.660 5.421 5.197 4.988 7.793 4.611 4.439 13 6.750 6.424 6.122 5.842 5.583 5.342 5.118 4.910 4.715 4.533 14 6.982 6.628 6.302 6.002 5.724 5.468 5.229 5.008 4.802 4.611 15 7.191 6.811 6.462 6.142 5.847 5.575 5.324 5.092 4.876 4.675

16 7.379 6.974 6.604 6.265 5.954 5.668 5.405 5.162 4.938 4.730

17 7.549 7.120 6.729 6.373 6.047 5.749 5.475 5.222 4.990 4.775 18 7.702 7.250 6.840 6.467 6.128 5.818 5.534 5.273 5.033 4.812 19 7.839 7.366 6.938 6.550 6.198 5.877 5.584 5.316 5.070 4.843 20 7.963 7.469 7.025 6.623 6.259 5.929 5.628 5.353 5.101 4.870

© 2022 Walden University, LLC Page 3 of 3

Remember: IRR calculations rely on the same formula as NPV does, although the IRR is not the actual dollar value of the project. Instead, it is the annual return that makes the NPV equal to zero. Step 1: Gather the following information to calculate the IRR using the Annuity Table (Table 1):

• Initial Cost (Investment)

• Net Annual Cash Flow

• Factor (PV of annual cash flows discounted at interest rate)

• Useful Life (Period) Step 2: Input the known values using the following information:

• Initial Cost (Outlay for Investment) = −5,000,000

• Net Annual Cash Flow = 820,000

• Factor = Unknown

• Useful Life (Period) = 20 years

Step 4: Divide the Initial Cost (Investment) by the Net Annual Cash Flow to get an Approximate Factor.

• Initial Cost (−5,000,000) / Net Annual Cash Flow (820,000) = Approximate Factor (−6.097)

Step 5: Find the years of service (Periods) row that matches the Useful Life (Period) of 20 years, and then follow this line across until you find an Approximate Factor close to −6.097. The factor closest to −6.097 equals −5.929. Step 6: Scan up the Useful Life of 20 years from the Factor of 5.929 to find the Interest Rate of 16%. This is the IRR.