Capital budgeting in Excel
1. Capital budgeting in Excel
Hello! In this screencast we will review how to calculate the net present value and internal rate of return for capital budgeting. Let’s say we want to find the net present value of this project. The net present value is simply the sum of present value calculations. To demonstrate this, we can calculate the present value for each cash flow. Since the PV formula creates negatives, we'll start with a minus sign in front of our formula. We’ll use a 10% discount rate and reference the year as the number of periods and the cash flow as the future value. Now drag the formula across for all four years. Great! We have all the present values for each year, so the last step is to sum them together. Boom! There it is. Excel has the NPV formula which does this very easily. It’s important to note that one limitation of the NPV formula is that the first value it receives is discounted as if it’s in the first period. Since we have a period 0, we cannot include this in our NPV function. Instead, we’ll need to add it to the NPV function. Now we type in the discount rate we want to use, and then reference the values. We can either do this separately or as a data range. Since our values are all in the same row, we can just select this data range. Nice! The internal rate of return, or IRR, is the discount rate that makes NPV equal to zero. Finding the IRR is an iterative function, which means that it's a guess-and-check problem, and it takes work to figure it out by hand. Luckily, we have computers that can run different discount rates until NPV equals zero. In Excel, we can use the IRR function. All we need to do is reference the range of values. Guess is optional; all it does is tell the function where to start guessing. If left blank, it will default to 10%, so this is okay to leave it blank. Now that we have our IRR, we can see how this works at discounting the net present values to 0. If we use the internal rate of return as the rate in our NPV function, we’ll see that NPV equals 0. These functions are great for the basic time value of money calculations, but if we have dates associated with our values, we can use more precise calculations, like XNPV and XIRR. The major difference between these X functions and their simple counterparts is that they can find the days between each period and they automatically compound daily to give an exact answer. Let’s see it in action! First, we need to create a date range, and I want the periods to be a year apart. Let’s start with a date, January 31, 2024. We can easily add a year with the EOMONTH function. EOMONTH stands for “end-of-month”. This function finds the date that is the end of the specified month, and we can add or subtract months. So we’ll reference the date in the previous cell, then add 12 months, and now we can easily drag this formula across to get a complete date range. Okay, now that we have a date range, we can use the X functions. Starting with XNPV, we’ll use a 10% discount rate, then we select our values, and then our dates. And that’s it! And XIRR isn’t too complex either; we only need the range of values and the date range. Notice that there is a slight difference between the X functions and their counterparts. This should be expected since the X functions are compounding daily and giving a more exact answer. Because XNPV and XIRR use day ranges, they are more precise and should be used when possible. Okay, now that you’ve seen it, it’s your turn to try it.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.