{"id":1508,"date":"2023-04-15T04:31:12","date_gmt":"2023-04-15T04:31:12","guid":{"rendered":"https:\/\/kpwalters.com\/?page_id=1508"},"modified":"2023-08-14T14:45:56","modified_gmt":"2023-08-14T14:45:56","slug":"npv-irr-excel","status":"publish","type":"page","link":"https:\/\/kpwalters.com\/index.php\/other-projects\/npv-irr-excel\/","title":{"rendered":"NPV and IRR in Excel"},"content":{"rendered":"\n
\n
Understanding NPV
and IRR
through Excel<\/h5><\/div>\n<\/div>\n\n\n\n
\n
\n

Last update June 8, 2023<\/em><\/p>\n\n\n\n

Net present value<\/a>
Discount rates<\/a>
Discounted cash flows<\/a>
Internal rate of return<\/a>
Excel files for download<\/a><\/p>\n<\/div>\n\n\n\n

\n
Overview<\/h5><\/div>\n\n\n\n

I have helped people with Microsoft Excel for almost 30 years.<\/p>\n\n\n\n

Internal rate of return (IRR) and net present value (NPV) were two subjects that many of my \u201cstudents\u201d struggled with. Initially, I confess that I, too, had difficulty in understanding the two concepts.<\/p>\n\n\n\n

However, laying things out in Excel makes it easier to see how these two important financial concepts work.<\/p>\n<\/div>\n\n\n\n

\n

Excel<\/em><\/p>\n<\/div>\n<\/div><\/div>\n\n\n\n

\n
<\/div>\n\n\n\n
\n
Net present value<\/h5><\/div>\n\n\n\n

When I think about net present value, the first thing I need to remember is that a dollar today is worth more than a dollar tomorrow. This is known as the \u201ctime value of money.\u201d Another way to think of that is to think about prices: will a loaf of bread cost more in five years or less? We all know that, on the whole, if you pay a dollar for something today, you will need more than a dollar to buy the same thing five years from now.<\/p>\n\n\n\n

Understanding net present value proceeds from that starting point. If you invest $100 today in something, you should receive more than a $100 back, and not just because you want to make money off your investments. So if we imagine putting $100 into an investment\u2014I\u2019ll start calling it a \u201cproject\u201d\u2014if we imagine putting $100 into a project, we want to receive more than $100 back from that project.<\/p>\n\n\n\n

Let\u2019s take, say, an investment in a friend\u2019s business. Imagine giving her $100 and receiving payments back from her over time. If she pays you $25 a year over five years, you\u2019ve received $125 back, but was that a good decision?<\/p>\n\n\n\n

The answer lies in the other options you had for your money.<\/p>\n\n\n\n

Discount rates<\/h5><\/div>\n\n\n\n

So, let\u2019s now introduce \u201cdiscount rates.\u201d I think of discount rates as having to do with these other options and what you might earn from those options. For example, instead of giving your friend the $100, you might buy a five-year treasury bond. For the purposes of net present value discussions, we\u2019ll call the interest rate on the treasury bond\u2014say 3.5%\u2014the \u201cdiscount rate.\u201d You could have a $100 treasury bond that increases in value twice a year, at an annual rate of 3.5% \u2014 therefore 1.75% every six months\u2014over the five years.<\/p>\n\n\n\n

Now let\u2019s take a look at both of these \u201cprojects\u201d in Excel. We\u2019ll start with the Treasury bond. The following table shows how the $100 investment grows over time. It\u2019s important to keep in mind that you are not receiving any payments. At the end of the five years, you can cash in the bond and receive $118.94. Your money has grown in value by $18.94.<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

Now let\u2019s take a look at your investment in your friend\u2019s business. She has been paying you back $25 a year for five years. You\u2019ve collected a total of $125, so it would appear that you\u2019ve done better than the treasury bond by $6.06.<\/p>\n\n\n\n

Discounted cash flows<\/h5><\/div>\n\n\n\n

But that\u2019s not a complete understanding. You\u2019ve received five $25 payments over time. Remembering my comment about the time value of money, we should recognize that the first $25 payment is less valuable than that same amount a year earlier, when the project started. But it\u2019s also worth more than waiting five years to receive the money (the example of the treasury bond). Because we want to know how much our investment is worth in today\u2019s <\/em>dollars, not five years from now \u2014 after all, it\u2019s net present <\/em>value \u2014 we will assess its value at the start of the project.<\/p>\n\n\n\n

How much less the $25 is worth depends upon what we use to \u201cdiscount\u201d its value. For this example, we will use the rate that we applied to the treasury bond, 3.5%. We could use something else, such as, say, an estimation of inflation rate, but that makes our understanding of the treasury bond comparison much more complicated. We\u2019re comparing against the treasury bond\u2014let\u2019s just use that rate.<\/p>\n\n\n\n

So, in year one, the present <\/em>value of the $25 is actually $24.15 using the discount rate from the treasury bond. The present<\/em> value of the payment our friend makes in year two is actually $23.34. And so on, as shown in column three below.<\/p>\n\n\n\n

<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

It\u2019s easy for me to imagine that you\u2019re now thinking that the treasury bond returned $18.94 but the loan returned $12.88. I\u2019ll explain this further in a moment, but what the net present value shows us is the comparison between two investment projects. The loan to the friend did $12.88 better than the treasury bond. What we did not <\/em>show is that using the discount rate of 3.5%, the treasury bond returned nothing! Stated differently, if inflation were 3.5%, the $100 we had at the start is just as valuable as $118.94 after five years.<\/p>\n\n\n\n

So the loan to your friend was better not just because it returned a total of $125, but also because the payments received throughout the five years increased the value over the treasury bond example.<\/p>\n\n\n\n

A second project<\/h5><\/div>\n\n\n\n

But now let\u2019s add one more scenario. Another friend has asked you to invest in his business. He wants the same $100, but he\u2019s promising a different repayment plan. He will repay you $10 for four years, then $90 in the last year. So he\u2019s promising a total of $130 in payments. Should you do it?<\/p>\n\n\n\n

Here\u2019s the layout. Once again, we will use the treasury bond example for our discount rate. (It doesn\u2019t matter too much what rate you use when comparing projects like this, as long as you\u2019re consistent between them.)<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

This example shows a net present value of $12.51, meaning that this investment project has a $12.51 advantage over the treasury bond example. However, this project is not as good an investment as the other opportunity, as that investment had a net present value of $12.88. So even though the payments total $130 versus $125, the discounted values of the payments reduce the overall value of the project.<\/p>\n\n\n\n

Internal rate of return<\/h5><\/div>\n\n\n\n

The next thing we should discuss is the internal rate of return (IRR). I think of the internal rate of return as the discount rate at which two projects are identical in value. <\/p>\n\n\n\n

This means that we solve the problem iteratively: we have to keep plugging in discount rates until net present value equals zero. And remember, net present value equaling zero simply means that two different investment projects with the same discount rate are of equal value in terms of their present <\/em>value, the value of our money today.<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

(Right click and choose \u201cOpen Image in New Tab\u201d to enlarge.)<\/em><\/p>\n\n\n\n

I used Excel\u2019s Goal Seek feature. I indicated that I wanted the adjusted gain (or net present value) to be zero by changing the discount rate. Goal Seek did the rest.<\/p>\n\n\n\n

The first project has an internal rate of return of 7.93% and the second project has an internal rate of return of 6.49%.<\/p>\n\n\n\n

NPV vs IRR<\/h5><\/div>\n\n\n\n

It is tempting to suggest that the internal rate of return determines the better investment. It is not my intent to show here how that is not always the case. (I have another workbook that attempts to show how IRR doesn\u2019t always indicate the best investment. At some point, I may write up a project to explain why IRR does not determine the better project.)<\/p>\n\n\n\n

For now, just remember that net present value is the measurement that truly determines which investment project offers the better return.<\/p>\n\n\n\n

Excel files<\/h5><\/div>\n\n\n\n

There are two files of possible interest to my reader.<\/p>\n\n\n\n

The first is the workbook I used for this explanation. Click here<\/a>.<\/p>\n\n\n\n

The second is a workbook with several formulas: array formulas, formulas using NPV, IRR, XNPV, and XIRR. It also includes a graphical representation that shows how internal rate of return does not always determine the better investment. Click here<\/a>.<\/p>\n<\/div>\n\n\n\n

<\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

Understanding NPVand IRRthrough Excel Last update June 8, 2023 Net present valueDiscount ratesDiscounted cash flowsInternal rate of returnExcel files for download Overview I have helped people with Microsoft Excel for almost 30 years. Internal rate of return (IRR) and net present value (NPV) were two subjects that many of my \u201cstudents\u201d struggled with. Initially, I …<\/p>\n

NPV and IRR in Excel<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":933,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"page-builder","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"disabled","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"enabled","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"kevin.p.walters@live.com","author_link":"https:\/\/kpwalters.com\/index.php\/author\/kevin-p-walterslive-com\/"},"uagb_comment_info":0,"uagb_excerpt":"Understanding NPVand IRRthrough Excel Last update June 8, 2023 Net present valueDiscount ratesDiscounted cash flowsInternal rate of returnExcel files for download Overview I have helped people with Microsoft Excel for almost 30 years. Internal rate of return (IRR) and net present value (NPV) were two subjects that many of my \u201cstudents\u201d struggled with. Initially, I…","_links":{"self":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1508"}],"collection":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/comments?post=1508"}],"version-history":[{"count":35,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1508\/revisions"}],"predecessor-version":[{"id":1736,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1508\/revisions\/1736"}],"up":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/933"}],"wp:attachment":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/media?parent=1508"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}