Is there a formula that will calculate the last cash flow from a string of irregular cash flows to result in a predetermined IRR?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Reverse IRR
0
Answers
0
Selected Answer
This is a perfect use-case for Solver.
First, install Solver. (Click the File button or Office button > Add Ins > Look to the bottom of the window and select Excel Add-Ins from the drop-down menu and click Go > Put a check mark next to Solver Add-in and click OK.
Next, use solver:
- Go to the Data tab and look to the right and click Solver.
- Next to Set Objective: click inside that input box and then select the cell that calculates the IRR.
- On the next line click Value Of: and in the input to the right type 0.05 for 5%.
- Click the input under where it says By Changing Variable Cells: and select the cell that you want to change in order to achieve the desired IRR (the yellow cell in your sample file).
- Click Solve
- Click OK in the next window that opens.
Now you should have around 7,784.44 for the last value, which gives you a 5% IRR.
Discussion
Don: This appears to be perfect, EXCEPT, I am using Office 365 and I can't figure out (I can't even find) how to install the Solver Add-In. I went to File/Options/Add-Ins and there were very few addins there. No hint of Solver. Searched and still no clue. Does 365 allow the Solver add-in or am I missing something obvious. BTW, it is not in my "Data" tab either.
mrandall (rep: 4) Jul 19, '16 at 3:56 pm
It should be the same. Here are the instructions to add it from Microsoft: Microsoft instructions
And here is an example using Excel 2010 from this site, with images: Install Add-In (this refers to the Analysis Toolpak but you can also see the Add-in for Solver in Step 5.
And here is an example using Excel 2010 from this site, with images: Install Add-In (this refers to the Analysis Toolpak but you can also see the Add-in for Solver in Step 5.
don (rep: 1989) Jul 19, '16 at 4:25 pm
Don: Thanks. The solver works. One more question, I am trying to have this cell automated so I can do quick sensitity analyses without having to manually run the Solver each time. Is there a way you know of to automate this cell?
mrandall (rep: 4) Jul 19, '16 at 5:12 pm
It should be pretty easy but that is a different subject than this question. Select this answer as the answer if it worked and then ask another question for automating Solver through vba. (I'm trying to keep separate topics in their own questions so it will be easier for future users to find answers to their questions.)
don (rep: 1989) Jul 19, '16 at 9:28 pm