Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.
Investing money can become quite complicated. For example, you might need to borrow the money you invest in some other project. You could also reinvest any proceeds from that project into another investment and get a gain on that money. If you need to calculate the internal rate of return for a series of mixed cash flows then you can use the MIRR function. The MRR function has arguments which are the interest rate on the initial loan, and the interest rate on any reinvested funds, and then also the cash flows themselves.
So in this worksheet I have the interest rate on the initial loan. So let's say that you have a construction project and you borrow this money for use in that project. And then let's say that you are getting money back on your investment and the interest rate on the reinvested funds is 5%. So in other words, you're borrowing $150,000 and then you're paying 9% per year on that and then all the moneys you get back, which are in cells B7 to B13, you're receiving interest on.
So let's go ahead and create our formula in cell C16. Now type =mirr( and then we have the values, which are in cells B6 to B13. And note that the value in cell B6 is in parentheses. That's because it is a negative number because it's an outflow from your account. Money that you no longer have use of. So we have B6 to B13, then a comma, and then we have the finance rate, which is the interest rate on the loan that is in cell C2, and then we have the interest rate on reinvested funds, which is in cell C3.
Type a right parenthesis. Check to make sure everything looks right. It does. Then press the Tab key and we see that the internal rate of return on this investment is about 6.5%. With that information you can evaluate this investment against others such as government bonds and if you get a higher rate of return than you would on a guaranteed investment you should take the plunge.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.