Anyone knows any trick to replace excel solver function? For example, if one needs to solve for premium with given 5 year of future equity cash flow to discount at target IRR, then how would you quickly get the premium needed without solver in excel?
I don’t know there is a super fast way. For IRR problems, I just make a cell for the IRR I am solving for, and another cell for PV(Equity Flows) which we want to be zero. Then I trial and error the IRR for about 30 seconds to zero in on the solution.
Can’t you just use your BAII plus for this? Or do you not plan to bring it to the exam?
1 Like
For other problems with complicated algebra I have made a table off to the side once the variables are isolated to one side of the equation. One column is the equation with the variables and the other side is possibilities for the variable. That way you can just drag the formula down and check a lot of options quickly, then narrow down the increment you are increasing by once you narrow down the range. This is what I plan to do if a quadratic comes up for example.
For IRR problems where you have to solve for premium, I just work all the way through the problem with premium as a variable and then you can solve for it at the end. It shouldn’t be that complicated algebraically.
Just to add to what’s already been said:
If it’s simple enough, I’d set up the algebra to solve for the target variable(s).
Else, if it’s a single unknown, I’d do trial and error / a binary search, perhaps via macro, perhaps manually.
Else, if it’s multiple unknowns, I’d write a macro to do trial-and-error.
Else, if it’s an exam question in spreadsheet format with macros not available and time limited, I’d be very tempted to write out notes on how I’d do trial-and-error to find a good solution after setting up the formulas/spreadsheet to do just that, and mark the question to come back to later to solve better if there’s time.
I agree with all of this.
The safest, if you can bracket the solution, is something like a binary search. It will converge. Anything other than some kind of bracketed search has the possibility of not behaving well.
1 Like
Thank you everyone for all the comments! Here is the reason why I asked about this.
When I was doing PVI/PVE or questions on Robin IRR paper, have seen a lot of questions asking to solving for premium or IRR. Found it annoying/time consuming in Excel to write down all the different parts of cash flows with P typed out & sum/sumproduct formulas wont work normally.
So as suggested, I would go with a dummy P to start with and use try/error to get the correct answer. And if to solve IRR, can definitely work with one column of a range of rates + one column of PV.
Thanks again everyone!
This is exactly how I do those problems and exactly how I plan to do it should it be on the exam. I will highlight the dummy cell and write out somewhere that I chose P so that IRR= some given %.
If you’re dealing with a reasonably small set of CFs, I think the CF worksheet and the IRR function on a BAII would save you the time and heartache of doing a binary search: the IRR function will do all that iterative stuff for you!!! You can still set up columns of time t, CFs and discount factors in the Excel worksheet and use whatever IRR the calculator spits out.
ETA: I took a quick look at the Robbins paper. Do you have to take off expenses, claims, and all that 
from P first? If so, you might be stuck with a binary search. 
Good suggestion on the calculator! BUT feeling a bit overwhelming to switching between calculator, computer or possibly paper during the real exam …
And yea … need to consider all those expense, claims, investment income earned on investable assets which are depending on UPR etc.
The calculator is really quick for IRR calculations and for bond problems. There is also an excel formula to solve for IRR directly. I would really recommend learning one of those two methods before the real exam. You can always plug in the IRR that you solved for into the original equation to check that your method worked.
FWIW, here are the specific Excel functions you can use for IRR-related calculations:
MIRR (periodic, and different reinvest/borrow rates)
XNPV (not necessarily periodic)
XIRR (not necessarily periodic)
all the financial functions are here:
use IRR and XIRR formulas in Excel. And then trial and error. It’s super quick.
You need to be very careful with NPV formula as it is to time 1.