Spreadsheets

XIRR in Excel: finding the rate on payments made on irregular dates

Use XIRR in Excel or Google Sheets to find the yearly rate hidden in payments made on uneven dates, such as a family loan repaid in parts or a chit fund.

Editorial illustration for XIRR in Excel: finding the rate on payments made on irregular dates

XIRR gives the yearly rate that fits a list of cash flows on different dates: =XIRR(values, dates). Enter money you pay out as negative and money you receive as positive. If you lent ₹1,00,000 and got back ₹25,000, ₹30,000 and ₹55,000 on three later dates, =XIRR(B2:B5, A2:A5) returns about 14.5% a year for that lender.

How to lay out the cash flows

Two columns, one row per payment, earliest first:

Date (column A)Cash flow (column B)What it is
5-Jan-2026-100000Lent out
5-Apr-202625000First repayment
20-Jul-202630000Second repayment
12-Jan-202755000Last repayment

In cell B6 type =XIRR(B2:B5, A2:A5). Format B6 as a percentage. The borrower, who sees the same flows with the signs swapped, gets the same rate as a cost.

Why are the signs and dates so important?

  • XIRR needs at least one negative and one positive value; with all positive or all negative it returns #NUM!.
  • Dates must be real dates, not text. If a date is left-aligned in the cell, it is probably text.
  • Keep the first row as the earliest date. Later dates can be in any order.
  • It uses the actual number of days in a 365-day year, so a payment 90 days later counts for less time than one 180 days later.
  • Both Excel and Google Sheets take the same arguments; an optional third argument is a guess to help it find the answer.

What does an uneven repayment really cost?

The same loan could be quoted as a flat total: you gave ₹1,00,000, got back ₹1,10,000. That sounds like 10%. XIRR says about 14.5% because the money was out for about a year, but you got part of it back early. Money returned sooner is worth more, and XIRR captures that where a simple total does not.

Can it handle a chit fund?

Yes, with care. List each monthly instalment you pay as a negative on its date, and the pot you receive as a positive on the date it arrives. Say you pay ₹5,000 on the 1st for 10 months from March 2026 and receive ₹46,000 in June after the foreman's deduction. XIRR comes out near 127% a year. That large figure is not an error: you used a lump sum early and repaid it in small pieces over a short period, and annualising a short period inflates the number.

XIRR or IRR: which one fits?

IRR assumes the payments are equally spaced, one per period, so it suits a clean monthly list with no gaps. The moment a payment is skipped, delayed or made on a random date, IRR quietly gives the wrong answer and XIRR is the right choice, because it reads the actual dates. If you do use IRR on monthly data, the result is a monthly rate; multiply carefully, and compare it with the XIRR figure as a sense-check.

What XIRR does not tell you

  • Whether a lender is trustworthy or a chit is well run.
  • The legal position of a private loan.
  • Which investment to pick; it only reads back the rate hidden in flows that have already happened or been agreed.
  • What happens if some repayments are missed: change the cash-flow list and run it again.

A short routine

  1. Record each payment the day it happens, with amount and date.
  2. Keep signs consistent: out is negative, in is positive.
  3. Run XIRR once the list changes, not before.
  4. Save the sheet beside the agreement or message thread.

If the loan or chit instalments move through your bank account, SpendixAI can import the statement so the dates and amounts you type into this sheet come from your records, not from memory.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports