Calculate IRR for Principal & Interest payments

Originally Posted by charles f

How do I write a formula to calculate the annual irr for a series of principal and interest repayments where the payments come in either monthly or quarterly on an inconsistent basis?

Hello Charles

If the repayments are monthly or quarterly then you can simply use the RATE function of Excel assuming the loan repayment are in constant amount

=RATE(NPER, PMT, PV, FV, type, Guess)

Where NPER will be the number of repayments

PMT will be the periodic (monthly or quarterly) repayment (principal+interest) This will be a negative amount as it is an outgoing cash flow

PV is the amount of loan a positive amount

FV for loans is usually ZERO

type should be 0 for end of month or end of quarter repayment

Guess is the interest rate the refines the search for interest rate when one is not found using the default rate of 10%

If your cash flows are monthly, quarterly and the amounts of repayments are not in constant amounts then you would have to use the IRR function

=IRR(cash flows, guess)
cash flows will include a positive amount for loan amount and negative amounts for loan repayment

Both RATE and IRR will report a periodic rate and if you want an annual rate then you would either do this

=RATE(NPER, PMT, PV, FV, type, Guess) * 12

=IRR(cash flows, guess) * 12

If you seek an annualized rate then you would do this for monthly repayments

=(RATE(NPER, PMT, PV, FV, type, Guess) ^ 12) - 1

=(IRR(cash flows, guess) ^ 12) - 1

And for quarterly payments you would do this


=(RATE(NPER, PMT, PV, FV, type, Guess) ^ 4) - 1

=(IRR(cash flows, guess) ^ 4) - 1


Now if the cash flows or loan repayments are on inconsistent basis then you would have recourse to using XIRR function. XIRR is the internal rate of return for cash flows that are not necessarily periodic

=XIRR(cash flows, dates, guess)
cash flows are the incoming loan amount (+ve) and outgoing loan repayment (principal+interest) a -ve amount

XIRR replies back with an annualized IRR

You may want to read up on these financial tutorials, Excel functions on this site, and find more details about TADXL Excel add-in for finance