For equally spaced monthly cash flows in B2:B14, enter =IRR(B2:B14) to calculate the rate per month; the first value is month zero and each following value is one month later. To express that result as an effective annual rate, convert it with =(1+C2)^12-1 if the monthly IRR is in C2.
What Excel IRR means for monthly cash flows
Excel’s IRR function calculates the internal rate of return for a sequence of cash flows that occur at regular intervals. The function does not know whether those intervals are days, months, quarters, or years. You establish the timing by how you arrange the values: if each cell represents one month, the returned rate is monthly.
IRR is the rate that makes the net present value of the cash-flow sequence equal to zero. In practical terms, it finds the periodic discount rate at which the value of the money going out balances the value of the money coming in. A negative amount commonly represents an initial investment or loan advance, while positive amounts represent receipts or repayments from the perspective of the person providing the money.
That timing convention matters. A monthly IRR is not an annual IRR, and Excel will not automatically multiply it by 12 or label it as monthly. Format the result as a percentage, but also label the cell clearly—for example, “Monthly IRR”—so it is not mistaken for a yearly return.
Arrange the cash flows in monthly order
Put one period’s net cash flow in each cell, in chronological order. Include the initial cash flow as period zero, then list each succeeding month. Use negative values for amounts paid out and positive values for amounts received, consistently from one point of view.
- Put the initial investment, advance, or purchase amount in the first period.
- Use one row or column for each later month, even if there is no transaction in that month.
- Enter zero for a month with no cash flow rather than leaving a gap in the timeline.
- Include both positive and negative values; a series with only one sign cannot produce a meaningful IRR.
For example, an investor’s initial outlay is negative from the investor’s perspective. Later distributions to that investor are positive. If you instead analyze the borrower’s cash flows, the loan proceeds are positive and repayments are negative. Reversing every sign leaves the rate unchanged, but mixing perspectives can reverse signs inconsistently and make the result misleading.
Dates in a neighboring column can help you audit the monthly sequence, but standard IRR calculates from position in the range, not from those dates. If a month has no cash flow, a zero preserves its place in the sequence. Avoid deleting that month or compressing the entries, because doing so would make the periods appear closer together.
Worked example: a one-year investment
Suppose an illustrative investment costs $1,000 today and returns $1,100 exactly 12 months later, with no interim cash flows. Put -1000 in B2, zeros in B3:B13, and 1100 in B14; the range B2:B14 therefore represents month zero through month 12. With that layout, enter =IRR(B2:B14) in C2. Excel returns approximately 0.7974%, which is the monthly rate.
The zero entries are important: they show that the $1,100 arrives after twelve monthly intervals rather than immediately after the initial outlay. The result can be checked mathematically: compounding approximately 0.7974% for 12 months gives an effective gain of about 10%, taking $1,000 to $1,100. Small differences in the displayed value are due to rounding; Excel calculates with more precision than the four decimal places shown.
With the monthly IRR in C2, enter =(1+C2)^12-1 in C3 to calculate the effective annual rate. It returns approximately 10.00%. This is a compound annual equivalent, not a claim that the investment produces a cash payment every month. The example is for illustrating the spreadsheet calculation only, not a forecast or investment recommendation.
For a more typical pattern with several receipts, replace the zeros with the actual net cash flows for their respective months. IRR then solves for the rate across the whole sequence; it does not calculate a separate return for each payment or simply average the monthly percentages.
Convert monthly IRR to an annual rate
There are two common annual figures, and they should not be confused. Multiplying a monthly rate by 12 gives a nominal annualized rate. Compounding it gives the effective annual rate, which reflects earning or paying the monthly rate repeatedly during the year.
With the monthly IRR stored in C2, use =(1+C2)^12-1 for its effective annual equivalent. For instance, a monthly rate of 1% compounds to about 12.68% effective annually, not 12%. The simple multiplication 1% × 12 gives 12% nominal. Which figure is appropriate depends on the convention used in the comparison or agreement, so label the calculation and verify the stated terms.
For this reason, do not describe the output of =IRR(B2:B14) as an annual return merely because the workbook is used for a yearly analysis. The intervals between entries determine the period of the IRR. If each entry is monthly, its result is monthly. If cash flows are quarterly, the same function returns a quarterly rate.
IRR guesses and multiple possible answers
IRR uses an iterative calculation. Its optional guess is a starting point for the search, not a required estimate of the final answer and not a change to the cash flows. If omitted, Excel uses a default guess of 10% per period. For monthly data, that default is 10% per month, which may be far from the relevant solution in some cases.
With cash flows in B2:B14 and a 1% monthly starting guess, enter =IRR(B2:B14,0.01). The guess is written as a decimal rate: 0.01 means 1%. If Excel returns an error or an unexpected result, try a reasonable alternate guess and check the data and period order before assuming the calculation is wrong.
Some cash-flow patterns have more than one sign change—for example, an outflow, then inflows, followed by a later cleanup cost. Such a sequence can have multiple mathematical IRR solutions, or no useful solution at all. Different guesses may lead Excel to different roots. In those cases, do not select a result just because it looks attractive; inspect the cash-flow pattern and consider whether an NPV analysis at a chosen discount rate communicates the economics more clearly.
Common monthly IRR errors to check
A #NUM! result often means Excel could not converge to a solution from its starting guess, or that the cash flows do not contain at least one positive and one negative amount. Check the signs, confirm the range includes the intended final payment, and try a different guess where the pattern supports a meaningful solution.
- Missing months: Use a zero for each empty month so the period spacing remains intact.
- Unequal timing: IRR assumes equal intervals; monthly labels do not make dates that vary by several days equally spaced.
- Wrong sign convention: Review every amount from a single participant’s point of view.
- Annual rate mistaken for monthly: State the unit beside the result and annualize only when needed.
- Amounts stored as text: Make sure the cash-flow cells are numeric values, not text that merely looks like a number.
- Fees or taxes omitted: Decide which cash flows belong in the analysis; IRR only uses the amounts included in its range.
Also distinguish a cash flow from an account balance. IRR expects period-by-period amounts paid or received, not the balance remaining at the end of each month. For a loan, for example, listing the original principal as a receipt and each repayment as an outflow is different from listing the loan balance after each payment.
When XIRR is better than IRR
Use IRR when the periods really are regular—for example, a payment on the same schedule each month—and the data contains one entry for each period. If cash flows occur on actual dates that are irregular, use XIRR instead. XIRR calculates an annualized rate using the supplied dates and a 365-day year; its output is not a monthly rate.
With cash-flow amounts in B2:B14 and their corresponding actual dates in A2:A14, enter =XIRR(B2:B14,A2:A14). Keep each date aligned with its amount, and include an initial date and cash flow. XIRR is useful when payments arrive on varying calendar days or when months have different lengths. If you need a monthly equivalent of an XIRR result in D2, enter =(1+D2)^(1/12)-1; that is a compound monthly equivalent, not a replacement for modeling the actual uneven dates.
For a loan or investment with contract-specific timing, fees, compounding conventions, or other terms, check the actual documentation and confirm the calculation with the lender or a qualified professional. This example explains spreadsheet mechanics and is not financial, tax, investment, or legal advice.
Frequently Asked Questions
Does Excel IRR return a monthly rate automatically?
It returns a rate for each interval represented by adjacent cash-flow entries. If the entries are one month apart, the rate is monthly; Excel does not infer the interval from dates or add a unit label.
Can I leave months with no cash flow blank?
Enter zero to make the timeline explicit and preserve the intended number of periods. A blank can be treated differently from a numeric cash flow in calculations, so zero is the clear choice when a month has no transaction.
Should I multiply monthly IRR by 12?
That gives a nominal annualized figure, not the effective annual rate. To account for monthly compounding, use the monthly-to-annual conversion shown above, (1 + monthly rate) raised to the 12th power, minus 1.
Why does my IRR show #NUM!?
Check for both positive and negative cash flows, missing periods, text-formatted amounts, and a range that omits a payment. IRR may also need another starting guess, or the cash-flow pattern may have no single useful solution.
Should I use IRR or XIRR for monthly payments?
Use IRR when the payments are at regular monthly intervals. Use XIRR when the actual dates are irregular; XIRR returns an annualized rate based on dates rather than a rate per monthly row.
Related
- Excel Max Date: What Is the Latest Date Excel Supports?
- Excel Max Columns: The 16,384-Column Limit Explained
- Excel Max Absolute Value: Find the Largest Magnitude
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sum by Category: Formulas and PivotTablesTo sum values by category in Excel, use SUMIF: with category names in A2:A100, amounts in B2:B100, and…
- Excel Attendance List: Setup, Formulas, and Practical TipsAn Excel attendance list is a worksheet that records who attended each class, meeting, or event and makes…
- SUMIF Function Examples: Text, Numbers, Dates in ExcelExcel’s SUMIF function adds numbers that meet one condition. With product names in A2:A6 and sales in B2:B6,…
- SUMIF Excel Examples With Two Criteria to Match RowsTo total values that match two conditions in Excel, use SUMIFS rather than SUMIF. With regions in A2:A8,…
- Excel Last Day of Month: Formula and ExamplesTo return the last day of the month for a date in Excel, use =EOMONTH(A2,0), where A2 contains…
- Excel ROW Function Examples for Numbering Rows and MoreExcel’s ROW function returns the worksheet row number for a reference, or the row containing the formula if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.