Calculating IRR by using Goal Seek Function in Excel
FOUNDER
What is Goal Seek?
• “Goal Seek” in Excel is a function to back calculate the input so as to get the desired output. • “It is very useful when one knows the desired result but do not know the inputs to reach to the desired result. • We can also refer it as back solving function.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Let us understand Goal Seek with the help of a simple example • Mr. John has USD 100,000/- in his bank account available for investment today. He wants to invest this amount in such a way that he can double it in 10 years. Tax rate in this example is 0%. • Here are the details:
Email: Support@skillfinlearning.com
Mobile: 9650010925
Will a bank deposit double his investment in 10 years? • Maturity amount if invested in a bank deposit can be calculated as follows:
• Principal amount = USD 100,000 • Rate of return = 4% • Time period (n) = 10 years • Maturity amount from bank deposit = USD 148,024/• This amount is significantly lower than what was originally planned (ie; USD 200,000) Email: Support@skillfinlearning.com
Mobile: 9650010925
Calculating IRR with the help of Goal Seek (1/4)
• We can calculate the IRR to double the investment amount in 10 years - Either manually by several iterations, or - with the help of Goal seek function in excel. • To use “Goal Seek” first calculate the return from the amount invested in bank deposit in Excel (as explained in the previous slide)
Email: Support@skillfinlearning.com
Mobile: 9650010925
Calculating IRR with the help of Goal Seek (2/4)
• Then Go to Data in the Menu Bar, Select What if analysis, then select Goal Seek from the drop-down menu.
You get the following Goal Seek arguments (see below)
Email: Support@skillfinlearning.com
Mobile: 9650010925
Calculating IRR with the help of Goal Seek (3/4)
“Set Cell” is the output cell (ie.; The maturity amount (Cell D6) in this examples).
“By changing cell” is the input cell to get the desired amount. We need the IRR in our example, so the input here is the rate of return (cell D4)
“To Value” is the desired output. (USD 200,000 in this example)
Select OK after entering all the inputs.
Email: Support@skillfinlearning.com
Mobile: 9650010925
Calculating IRR with the help of Goal Seek (4/4)
Goal Seek, calculates the rate of return of 7.2% to get USD 200,000/- required by Mr. John. That means that the internal rate of return has to be at least 7.2% to double the investment amount in 10 years Email: Support@skillfinlearning.com
Mobile: 9650010925
Want to learn more? Explore our “Financial Analysts Skills Training” (FAST) course Master the complete financial analytics and Excel modeling skills to get trained as a Financial analyst by an Expert instructor Includes: Annual (1 year) access Take the program anytime anywhere Direct access to the instructor Weekly mentorship assistance 10 hours on-demand HD videos 7 real life case illustrations 7 Practice assignment Access on Mobile and Tablet Certificate of Completion 30-day money back guarantee Email: Support@skillfinlearning.com
You get a Personalized Training Experience: Unlimited access to video content 1 on 1 coaching from expert instructor Individual mentorship assistance Dedicated doubts resolution sessions Practice on real world projects from industry experts Evaluation and feedback on assignments For more details please visit; Financial Analyst Skills Training (FAST)
Mobile: 9650010925
THANK YOU
Email: Support@skillfinlearning.com
Mobile: 9650010925
Visit us: www.skillfinlearning.com Chat with us: +91 96500 10925 Email us: support@skillfinlearning.com