In this article, I will discuss how to calculate IRR (internal rate of return) in Excel. I will focus on several topics like:.
Curious what goal seek does? Find out now, because it can save you time. Read the article: Excel's Goal Seek tool is in the. Sep 5, 2018 - Go to the Data tab > Forecast group, click the What if Analysis button, and select Goal Seek In the Goal Seek dialog box, define the cells/values to test and click OK: Set cell - the reference to the cell containing the formula (B5). To value - the formula result you are trying to achieve (1000).
Calculating IRR using Excel’s IRR & XIRR function. Calculating IRR using Excel’s Goal Seek feature. Calculating IRR manually or by hand.
How to calculate IRR from NPV (net present value). Using IRR function in Excel VBA. How to calculate IRR in Excel for monthly cash flow.
Calculating IRR for Uneven cash flow. How to calculate MIRR in Excel (modified internal rate of return). And what is a good IRR I hope you will enjoy the whole article. Let’s start with an investment proposal you got: Sam is your friend.
Comes to you with an investment proposal. You are proposed to invest $1000 and these are the cash flows you can expect for the investment over the next 5 years. There is a bank nearby your place and you trust this bank. And this bank provides 12% yearly interest on a deposit. Or you can invest your money in government saving bond that provides 10% interest rate/year. The government bond is more secure than depositing with a bank.
So, you have three options to invest your $1000:. You can invest in your friend Sam’s project. You can deposit the amount in your preferred bank that provides 12% interest rate per year. Or you can buy the government bond and enjoy more secured income What will you do? To make a better decision, you have to know, at first, the internal rate of return on your investment with Sam’s project. Let’s calculate the IRR of these cash flows. There are several scenarios of cash flows (it can be regular, discrete, monthly, etc.), so your approach will also be different when you will calculate IRR.
Table of Contents. 1) Calculating IRR (internal rate of return) using Excel’s IRR function (Cash Flow is Continuous/Regular) For our above example, cash flow is continuous. So, we can easily calculate IRR using Excel’s IRR function. In cell K11, I have placed this formula: =IRR(K4:K9) And you see the formula returns value 16% (the cell is formatted to percentage). IRR function is a very simple one to use. Syntax of IRR function: IRR(values, guess) As the values argument, we have passed this cell range K4: K9.
Values must contain at least one negative value and one positive value. Otherwise, the IRR function will provide #NUM! We did not pass any value for the guess argument, it is not necessary actually. So, this is one way. 2) Using IRR function in Excel VBA IRR function can also be used in Excel VBA. Dim IRRVALUE As Double Static CashFlow(6) As Double CashFlow(0) = -1000 CashFlow(1) = 200 CashFlow(2) = 500 CashFlow(3) = 200 CashFlow (4) = 150 CashFlow (5) = 500 IRRVALUE = Irr(CashFlow) This code will provide the same IRR value that we have found using IRR Excel function in Excel spreadsheet.
3) Calculate internal rate of return using Excel’s Goal Seek Feature To use Goal Seek feature to find the internal rate of return, you have to understand how to calculate the Net Present Value in Excel. What is Net Present Value? Money that you have now is more valuable than the money that earned later. If you have $1000 today, you can invest it in a business, or buy a product today and sell it later, or at least you can deposit it with a bank that gives you interest for your deposits. Say, you have a trustable bank nearby your location and they give 12% interest for your deposits.
You go to the bank and deposit your $1000 with the bank on 12% interest rate per year for next 3 years. Assume that you’re not withdrawing any money from the bank. After 1 year you will get: $1000+ $1000 x 12% = $1000 (1+12%) = $1120 So, $1120 after 1 year is the same as $1000 today.
After 2 years, you will get: $1000 (1+12%) + $1000(1+12%)(12%) = $1000(1+12%)(1+12%) = $1000(1+12%)^2 = $1254.40 So, $1254.40 after 2 years is the same as $1000 today. In the same way, we can conclude that after 3 years, we shall get: $1000(1+12%)^3 = $1404.93 So, $1404.93 after 3 years is the same as $1000 today. We can conclude an equation from the above discussion: Future value of the money, FV = PV((1+r)^n) Here, PV = Present value r = Interest rate/year n = Number of years Reversely, we can calculate the present value of the money with this equation: PV = FV/((1+r)^n) We shall use this formula to find the internal rate of return on future cash flows using Excel’s Goal Seek feature.
But before that, you have to know what is actually the internal rate of return? Let’s get back to our previous example.
For this set of cash flows, we got the internal rate of return 16% (using Excel’s IRR function). The value is actually 15.715% If you use this Internal rate of return to calculate the present values of your future cash flows, your initial investment will be balanced out, I mean -$1000 + sum of future cash flows = 0 = sum of future cash flows = initial investment So, IRR is that interest rate that balances out your initial investment and future cash flows from the investment. Let’s show you this mathematically. Here, r = 15.715% = 0.15715.
So, I think theoretically you got the whole idea. Using Goal Seek feature to find out the internal rate of return Excel’s Goal Seek feature takes three values: Set cell, To value, and By changing cell You can read it like set cell xxx to value xxx by changing cell xxx. Xxx will be replaced with appropriate values.
Look at the following image. From cells C3 to C7, we have calculated the present values of the future cash flows using this formula: PV = FV/((1+r)^n) I have used an arbitrary value 10% in cell F4. I have used 10%, you can use 5% or any value.
In cell C8, we have summed all those future values. Now, we shall use Excel Goal Seek feature to Set cell C8 (sum of all future cash flows) To value 1000 (equal to initial investment) By changing cell F4 (internal rate of return). And here is the setting. And this is what happens when I click on the OK button. Excel Goal Seek feature do the iterations and then comes up with a value that meets all the criteria. It is funny to use. And we get the same internal rate of return as we got using the Excel’s IRR function.
4) How to calculate IRR manually / by hand? I have shown you two ways of calculating IRR using Excel:. Using Excel IRR function.
And using Excel’s Goal Seek feature. Now I will show you how you can calculate IRR manually or by hand.
We shall use the same worksheet to show this method. In cell range C3 to C7, I have calculated the present values of the cash flows using this formula: PV = FV/((1+r)^n) And in cell C8, we have summed up all the present values of the cash flows.
Now, in cell F4, you have to change the value manually to get a nearby value that makes the total present values equal to 1000. Say, we change the value to 12%. You see the total present value is $1098. Now change the value to say, 16%. You see the value in cell C8 is 993.027. This is the manual way. It is not tough but it will take time to get a very close value from this method.
You can also try this method on paper, but that might take even a whole day to find the internal rate of return of some future cash flows. 5) How to calculate IRR from NPV (net present value) When we have shown you how to calculate IRR manually or using by hand in the above section, we have actually used net present value to calculate IRR. We have used this formula to find out the present value of future cash flows: PV = FV/((1+r)^n) Then we have gone through the trial and error method to calculate the IRR. 6) How to calculate IRR when cash flows are discrete, not equal periods?
When your cash flows are not regular, you will use Excel’s XIRR function to calculate the internal rate of return. Say, we have some discrete periods and the cash flows like the following image.
And we want to calculate the IRR from these values. In cell B16, we just put this formula: =XIRR(B2:B14,A2:A14). Syntax of XIRR Function: XIRR (values, dates, guess) XIRR function takes the cash flow values and dates of the cash flows and outputs the internal rate of return (4.39% in our case). I have formatted the cell with%, so you’re seeing the value in percentage. 7) How to calculate IRR in Excel for monthly cash flow In this section, I will show how to calculate IRR in Excel for monthly cash flows.
You are seeing some data in the following image. Our initial investment is -200000 and it is on 31st Dec 2017. Then we have shown every month’s cash flow. IRR function is used to calculate equally payment periods like years. You can use IRR function to calculate monthly cash flows but as all months are not equal in days, so it is better to use Excel’s XIRR function.
XIRR function is used to calculate the internal rate of return of cash flows that are not periodic. We have seen how to use XIRR function in the previous section. To show you the differences between IRR and XIRR, I have calculated both values. You see there is a difference between the values.
Note: When you will use IRR function to calculate the internal rate of return for monthly cashflows, you will multiply the IRR value by 12 as IRR has calculated the monthly rate of return, not yearly. 8) IRR for Uneven cash flows With the word “Uneven cash flows”, I mean cash flows that are not periodic. In this case, as we discussed in one of our above sections, you will use Excel’s XIRR function. 9) How to calculate MIRR in Excel (modified IRR) Say, you had taken a loan of amount $5000 at the interest rate of 12% a year to invest in a project.
The will give you periodic cash flows like the following image. What you do is you reinvest the cash flows that come from this existing project in a new project. The new project’s possible internal rate of return is, say, 20%. So, what is your actual (or modified) internal rate of return for this existing project? In this situation, you can use Excel’s MIRR function. In cell F22, we have used Excel’s MIRR function and the function returns 9.33% modified internal rate of return for this project. Microsoft Excel’s MIRR function returns the modified internal rate of return for a set of cash flows.
Syntax of Excel MIRR function: MIRR (values, financerate, reinvestrate) All the arguments are required. What is a good IRR?
It is a very tough question for answering. Normally IRR value is compared with a fixed value like Bank Interest Rate, or Government Bond rate as you can earn these incomes idly without doing anything and risking anything. And IRR is also compared to project to project basis. In the following image, you’re seeing data of two projects and their expected revenue for over the next 5 years.
After calculating the IRR of these projects, it seems that Project B would be more potential for the company. Download Working File.
I'm grasping at straws here, so any help would be great (ie. I have no idea what I'm doing with VBA).
I'm trying to solve a circular reference problem in Excel by creating a goal seek macro - basically. S3 = M + S2,. S3 = L. G. I want to goal seek S3 to equal L. G by changing M. So, I've put.
L. G in cell H32,. S3 in H18,.
M in G18,. S2 in F18 and this is what I've gathered so far. Sub GoalSeek Dim x As Integer x = Range('H32').Value Range('H18').GoalSeek Goal:=x, ChangingCell:=Range('G18') End Sub I'm getting a 'Reference is not valid' error, ideas? GoalSeek will throw an 'Invalid Reference' error if the GoalSeek cell contains a value rather than a formula or if the ChangingCell contains a formula instead of a value or nothing. The GoalSeek cell must contain a formula that refers directly or indirectly to the ChangingCell; if the formula doesn't refer to the ChangingCell in some way, GoalSeek either may not converge to an answer or may produce a nonsensical answer. I tested your code with a different GoalSeek formula than yours (I wasn't quite clear whether some of the terms referred to cells or values). For the test, I set: the GoalSeek cell H18 = (G18^3)+(3.G18^2)+6 the Goal cell H32 = 11 the ChangingCell G18 = 0 The code was: Sub GSeek With Worksheets('Sheet1').Range('H18').GoalSeek Goal:=.Range('H32').ValueChangingCell:=.Range('G18') End With End Sub And the code produced the (correct) answer of 1.1038, the value of G18 at which the formula in H18 produces the value of 11, the goal I was seeking.