To calculate a monthly internal rate of return in Excel, put cash flows in chronological order at equal monthly intervals, including the initial outflow as a negative value, then use =IRR(B2:B4) for values in B2:B4. Excel returns a rate per cash-flow period, so when each period is one month, the result is a monthly IRR.
What monthly IRR means
IRR is the discount rate that makes the net present value of a series of cash flows equal zero. In a monthly model, Excel treats each successive cell in the values range as one period after the previous cell. The first value is period zero; the next is one month later; the following value is two months later, and so on. The cells do not need to contain dates for IRR, but the cash flows must genuinely be spaced at regular monthly intervals.
A monthly IRR is not automatically an annual rate divided by 12. It is a rate for one monthly period, and compounding that rate for a year produces its effective annual equivalent. This distinction matters when comparing an investment with a quoted annual return, a loan rate, or another model that uses a different compounding convention.
IRR is a rate of return implied by the timing and amounts of the cash flows entered. It does not, by itself, describe risk, guarantee a future return, or account for assumptions that are missing from the spreadsheet. For a real loan or investment, check the actual terms with the lender or a qualified professional; this example is for spreadsheet illustration, not financial advice.
Set up monthly cash flows correctly
Use one cell per month and keep the order consistent. Put the initial investment or other payment made at the start in the first cell, usually as a negative amount. Put later receipts or payments in the cells corresponding to the month in which they occur. A simple layout might have the month labels in column A and the cash flows in column B:
- Month 0: initial outflow, entered as a negative number.
- Month 1: cash received or paid one month later.
- Month 2 and subsequent months: the next monthly cash flows, including zero if there was no cash movement.
With month labels in A2:A4 and cash flows in B2:B4, where B2 is the initial outflow, B3 is the month-one flow, and B4 is the month-two flow, use =IRR(B2:B4). The labels in column A are there for readability; IRR calculates from the values in column B and assumes equal spacing. Keep zero-flow months in the range so that Excel does not mistakenly treat the cash flows on either side as consecutive months.
Check the signs and timing before interpreting the answer. An investment paid at the beginning is normally negative from the investor’s perspective, while proceeds received later are positive. If your viewpoint is the borrower rather than the lender, the signs may be reversed, but the series still needs to represent one consistent perspective. A range with no negative cash flow or no positive cash flow cannot describe a conventional investment return for IRR to solve.
Worked monthly IRR example
Suppose an illustrative project costs 1,000 at the start, has no cash flow in month one, and pays 1,210 at the end of month two. Enter -1000 in B2, 0 in B3, and 1210 in B4. The zero is important: it tells Excel that the final receipt arrives two periods after the initial payment, not one period later.
With those cash flows in B2:B4, calculate the rate using =IRR(B2:B4). Excel returns 10% per month. The arithmetic checks because discounting the month-two receipt at 10% for two months gives 1210 / (1.10^2) = 1000, which offsets the initial 1,000 outflow. The net present value at that monthly rate is therefore zero.
If the monthly rate were repeated and compounded for 12 months, its effective annual equivalent would be calculated from the monthly result. With the same B2:B4 cash-flow layout, use =(1+IRR(B2:B4))^12-1. This returns about 213.84%: 1.10 raised to the 12th power is approximately 3.13843, and subtracting one gives 2.13843, or 213.84%. That annualized figure is a mathematical conversion, not a claim that a two-month project will actually repeat at the same rate for a year.
Convert monthly IRR to an annual rate
For a monthly IRR r, the effective annual rate is (1 + r)^12 – 1. This conversion assumes the same monthly rate compounds in each of 12 months. It is usually the appropriate way to compare periodic returns when the comparison uses an effective annual rate.
Multiplying a monthly IRR by 12 gives a nominal annualized figure, not the effective annual return. For example, 10% per month multiplied by 12 is 120%, while compounding 10% each month gives 213.84% effective annually. These are different conventions, so state which one you are reporting and match the convention of the rate you are comparing against. If you instead have an effective annual rate and need its equivalent monthly rate, take the 12th root of one plus that annual rate, then subtract one.
Do not annualize mechanically if the intervals are not monthly or if cash flows arrive on irregular dates. In those cases, the ordinary IRR result may not represent the intended period, and a date-based calculation is more suitable.
IRR versus XIRR for date-based cash flows
IRR assumes equally spaced periods. It does not read date labels next to the amounts, so a cash-flow list with dates on the 5th, 18th, and 30th of different months is not a reliable monthly IRR series just because the rows are called months. If actual transaction dates vary, Excel’s XIRR function uses the dates to calculate an annualized rate based on the timing of the flows.
With actual Excel dates in A2:A4 and the corresponding cash flows in B2:B4, use =XIRR(B2:B4,A2:A4). XIRR returns an annual rate; it does not return a monthly IRR. To express a positive effective annual XIRR result stored in C2 as an equivalent monthly compounded rate, use =(1+C2)^(1/12)-1, assuming C2 contains the XIRR output as a numeric percentage. This conversion is an equivalent monthly rate, not a recalculation of the dated cash flows as evenly spaced monthly events.
Use IRR when the cash flows occur at a regular monthly cadence and you want a per-month rate. Use XIRR when the actual dates matter, such as uneven payment dates or a first or final period that is shorter or longer than a normal month. In Microsoft 365 for Windows, both are worksheet functions; the formulas work the same way in Excel for Mac and Excel for the web. Make sure the XIRR dates are valid Excel date values rather than text that only looks like dates.
IRR arguments, guesses, and multiple answers
The IRR syntax is =IRR(values,[guess]). The values argument is the cash-flow range or array. The guess is optional; if omitted, Excel starts its iterative search with a 10% rate. You can supply a different guess when Excel does not converge, but changing the guess does not fix incorrect signs, missing periods, or a badly structured cash-flow series.
IRR is found iteratively, rather than by a simple averaging of cash flows. Some cash-flow patterns can have more than one mathematically valid IRR. This is especially possible when the cash-flow signs change more than once—for example, an initial investment, later proceeds, and then a major cleanup or closing cost. In such cases, different starting guesses may lead Excel to different solutions, and one reported percentage may not tell the whole story. Inspect the pattern and consider net present value at an explicit rate, or seek a method suited to the decision being analyzed.
A one-time switch from negative to positive is the usual investment pattern and is less likely to create multiple IRR solutions. Still, the result should be checked against the cash-flow schedule and an independent present-value calculation when the decision is important. A high IRR alone does not mean a project is better: scale, timing, reinvestment assumptions, and the amount of cash invested also matter.
Common monthly IRR errors
- Missing the initial outflow: If the investment is not included as a negative amount, the calculation no longer measures the return on that investment.
- Removing a zero month: Omitting a zero cash-flow period shortens the implied timeline and can change the calculated rate. Keep one cell for every monthly period.
- Mixing monthly and annual periods: A cash-flow range that includes monthly values in some rows and annual totals in others violates IRR’s equal-period assumption.
- Using text or blank cells unintentionally: Confirm the range includes numeric values and the intended zero months. A blank can be overlooked during a review even if the range shape appears correct.
- Getting #NUM!: Check that the range has at least one positive and one negative value, the periods are in order, and the values are plausible. If the cash-flow pattern supports a solution but the default search fails, try a reasonable guess in the optional second argument.
- Reading the output as a percentage incorrectly: A cell formatted as Percentage may display 0.10 as 10%. Formatting changes display, not the underlying rate. Avoid multiplying the formula by 100 if the cell is already formatted as a percentage.
When a result seems surprising, test whether its implied discount rate makes the cash-flow net present value approximately zero. Also verify the perspective and sign convention, the number of periods, and whether the intended result is monthly, annual effective, or annualized on an actual-date basis.
Frequently Asked Questions
Can Excel calculate a monthly IRR?
Yes. Use IRR on a range with one value for each equally spaced month. Excel returns the rate per interval, so monthly intervals produce a monthly IRR.
What formula gives monthly IRR in Excel?
If the monthly cash flows are in B2:B20, use =IRR(B2:B20). Include the initial investment as a negative value and retain zero-flow months in the range.
Should I use IRR or XIRR for monthly cash flows?
Use IRR if each cash flow is exactly one regular period after the previous one. Use XIRR if the actual dates are irregular; XIRR returns an annualized rate based on those dates, not a monthly rate.
How do I convert monthly IRR to annual IRR in Excel?
For monthly IRR in B1, use =(1+B1)^12-1 to calculate the effective annual equivalent. Multiplying B1 by 12 gives a nominal annualized figure and is not the same as compounding.
Why does Excel IRR show #NUM!?
Common causes include cash flows with only one sign, an unsuitable guess, or a range that does not represent the intended periods. Check the cash-flow signs and timeline first, then try a different optional guess if the series otherwise makes sense.