# Excel MIRR Function: Syntax, Examples & Tips - URL: https://betteranalyst.com/excel/mirr-function - Author: Better Analyst Formula Bot is now Better Analyst—same account, more powerful analysis.Formula Bot is now Better Analyst—same account and workflows, with more powerful analysis, dashboards, and automation. [Learn what changed](https://betteranalyst.com/formula-bot-is-now-better-analyst) The MIRR function calculates the modified internal rate of return using cash flows, finance rate, and reinvestment rate for investment evaluation. ```excel =MIRR(values, finance_rate, reinvest_rate) ``` Generate FormulaExplain Formula One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward. Describe the MIRR you need in plain EnglishGenerate Formula Need more than a single MIRR formula? Try our free [AI for Excel](https://betteranalyst.com/excel-ai) — it reads your entire spreadsheet, answers questions in plain English, and builds charts and analysis in the same chat. ## Parameters **`values`**:\ An array or range of cells that represent the cash flows for the investment, including both negative values (cash outflows) and positive values (cash inflows). **`finance_rate`**:\ The interest rate paid on the funds used in the investment, expressed as a decimal. **`reinvest_rate`**:\ The interest rate earned on reinvested cash flows, also expressed as a decimal. ## What is a MIRR Formula in Excel? The *MIRR* function in Excel is a financial tool that calculates the Modified Internal Rate of Return for a series of cash flows. Unlike the standard Internal Rate of Return (IRR), which assumes reinvestment of cash flows at the same rate, MIRR accounts for the cost of financing and the reinvestment rate, providing a more accurate reflection of an investment's profitability. In simpler terms, the MIRR function helps you evaluate the profitability of an investment by considering both the cost of capital and the returns from reinvested cash flows. ## A Practical Example Imagine you are evaluating a project with the following cash flows over a five-year period: ### Cash Flow Table: | Year | Cash Flow | | - | - | | 0 | -10000 | | 1 | 3000 | | 2 | 4200 | | 3 | 6800 | | 4 | 5000 | | 5 | 7000 | You want to calculate the MIRR for this investment, assuming a finance rate of 10% and a reinvestment rate of 12%. ## MIRR Formula To calculate the MIRR for the cash flows, you would use the MIRR formula as follows: ```excel =MIRR(B2:B7, 10%, 12%) ``` ### Breakdown of the Formula: - **B2:B7**: This is the range of cash flows, including the initial investment (negative value) and subsequent returns. - **10%**: This is the finance rate, representing the cost of borrowing or the cost of capital. - **12%**: This is the reinvestment rate, representing the rate at which cash flows are reinvested. ## Result of the Formula When you apply the formula, it calculates the MIRR based on the provided cash flows, finance rate, and reinvestment rate. The output would be a percentage that represents the effective annual return of the investment. | MIRR Result | | - | | 11.36% | Here, the MIRR indicates that the investment yields an effective annual return of approximately 11.36%. ## Why Use MIRR? The MIRR function is particularly useful for investors and financial analysts who want a more realistic measure of an investment's profitability. It allows for better decision-making by considering both the cost of financing and the potential returns from reinvested cash flows. ## Key Takeaways: - **MIRR**: Calculates the Modified Internal Rate of Return for a series of cash flows, accounting for financing and reinvestment rates. - **Realistic Investment Evaluation**: Provides a more accurate measure of profitability compared to standard IRR. - **Common Use Cases**: Ideal for capital budgeting, project evaluation, and investment analysis where cash flow timing and rates are critical. Understanding how to use the MIRR function can significantly enhance your financial analysis capabilities in Excel, enabling you to make informed investment decisions. Happy calculating! ## Example use case You have a table where Column A contains the years of an investment project and Column B contains the corresponding cash flows for each year. You can use the MIRR function to calculate the modified internal rate of return for the investment, taking into account both the cash inflows and outflows over the project's lifespan. This provides a clearer picture of the investment's profitability and helps in comparing it with other investment opportunities, allowing for more informed financial decision-making. **Beyond the MIRR function:** Better Analyst is a full [Excel AI](https://betteranalyst.com/excel-ai) that goes well past individual formulas. Upload any spreadsheet and ask questions in plain English — you'll get formulas, data analysis, and charts back in seconds. It's a free [AI Excel assistant](https://betteranalyst.com/excel-ai) that works with any .xlsx, .xls, or .csv file — no plugins, no Microsoft 365 required. Don't have a spreadsheet yet? Use our free [AI spreadsheet generator](https://betteranalyst.com/ai-excel-spreadsheet-generator) to turn a text prompt into a downloadable Excel file — a full [AI Excel sheet maker](https://betteranalyst.com/ai-excel-spreadsheet-generator) that builds headers, columns, and formulas from your description. ## Need more help with Excel formulas? Create a free account for formula help, explanations, and spreadsheet analysis. Usage limits depend on your plan. [Create Free Account](https://betteranalyst.com/signup)[Try Formula Generator](https://betteranalyst.com/excel-ai) ## Excel how-to guides - [How To Freeze Panes In Excel](https://betteranalyst.com/excel/how-to-freeze-panes-in-excel) - [How To Highlight Duplicates In Excel](https://betteranalyst.com/excel/how-to-highlight-duplicates-in-excel) - [How To Insert Checkbox In Excel](https://betteranalyst.com/excel/how-to-insert-checkbox-in-excel) - [How To Insert Multiple Rows In Excel](https://betteranalyst.com/excel/how-to-insert-multiple-rows-in-excel) - [How To Lock A Row In Excel](https://betteranalyst.com/excel/how-to-lock-a-row-in-excel) - [How To Lock Cells In Excel](https://betteranalyst.com/excel/how-to-lock-cells-in-excel) - [How To Make A Copy Of An Excel Sheet](https://betteranalyst.com/excel/how-to-make-a-copy-of-an-excel-sheet) - [How To Make A Graph In Excel](https://betteranalyst.com/excel/how-to-make-a-graph-in-excel) ## Explore all Excel formulas Browse our complete library of Excel formula guides Compare your options ## Decide with evidence, not a sales pitch. Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose. 1. [01ChatGPTOpen a balanced comparison prompt in ChatGPT.](https://chatgpt.com/?prompt=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 2. [02ClaudeOpen the same research prompt in Claude.](https://claude.ai/new?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 3. [03PerplexityResearch the comparison with linked web sources.](https://www.perplexity.ai/search?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.