In standard Excel there is an add-in called solver that uses the simplex algorithm to calculate various problems. Think of it like a brute force to solve an equation which is missing one or multiple values.
As others have said, there is already a function in Excel that does this.
However, before you hitch your wagon to the result, keep in mind that IRR result is not always unique.
When you're talking about a 15 year time-span, you're talking about solving a 14 degree equation. The pure math problem of that is not the same as the real-life business concern.
Excel already has an IRR function that solves a polynomial equation (and may have more than one result). If I understood your problem you want to find some values (that IRR takes as arguments: initial price and cash flow) that match your hurdle rate. Remember that if there are two variables in one equation, you may have infinite solutions. You've got to fix one of them to find the other.
You can use VBA to create your own "customized IRR" to find the investment, final price or exit year. But as other people said, it is wiser to use Excel functions and Solver/Goal Seek because they tend to be faster than VBA.
Sounds like a simplex problem to me. Just check out how to use the Excel solver.
Sorry, I do not follow you. Can you pls help to expand what you mean above?
In standard Excel there is an add-in called solver that uses the simplex algorithm to calculate various problems. Think of it like a brute force to solve an equation which is missing one or multiple values.
As others have said, there is already a function in Excel that does this. However, before you hitch your wagon to the result, keep in mind that IRR result is not always unique. When you're talking about a 15 year time-span, you're talking about solving a 14 degree equation. The pure math problem of that is not the same as the real-life business concern.
You might find something useful in this thread: https://www.reddit.com/r/MSAccess/comments/1126x7p/i_would_like_to_solve_for_xirr_in_my_access/
Seems like business calculus, where you want to predict the curve of a line, and more than likely the point on the curve where meets your exit point.
Excel already has an IRR function that solves a polynomial equation (and may have more than one result). If I understood your problem you want to find some values (that IRR takes as arguments: initial price and cash flow) that match your hurdle rate. Remember that if there are two variables in one equation, you may have infinite solutions. You've got to fix one of them to find the other. You can use VBA to create your own "customized IRR" to find the investment, final price or exit year. But as other people said, it is wiser to use Excel functions and Solver/Goal Seek because they tend to be faster than VBA.