How to Calculate the IRR for an Adjustable Rate Loan

One day, you may decide to do some real estate investing. If that time comes, you need a way to figure out your return on investment, or ROI. This is a relatively easy calculation to do. If you invest $100,000 in a property and sell it a few years later for $110,000, you've made $10,000 and your ROI is 10 percent. But some serious investors want to analyze their investments more deeply. That's where internal rate of return or IRR comes into play. IRR adds a layer to ROI by taking into account the time the property was held.

IRR goes by many names, but the concept means basically the same thing. The other names for IRR are APY, the average percentage yield; a loan's "effective interest rate"; the discounted cash flow rate of return -- DCFROR -- and simply rate of return -- ROR.

Calculating IRR is not easy in your head or even on paper. Even highly trained investors have to use spreadsheets and financial software to figure it out. The easiest way is to use a spreadsheet with the IRR function, like Microsoft Excel. In Excel choose a column and list the annual or monthly dates of your investments in order. Next to the appropriate dates enter the money you made from your investment and the money you spent on your investment. For example, for January of 2013, you made $200, but in March you spent $50 and so on. This figure should represent the combined amount -- meaning if you made $700 on rent, but spent $400 on your mortgage and repairs, you'd enter the amount of $300. Once you get all your amounts filled in for each time period use the XIRR function to calculate the IRR. This percentage shows you how much you earned on your investment during that time period.

If an investor uses an adjustable-rate mortgage to finance the investment property the cost of the loan varies as the interest rate goes up. The adjustable rate usually stays constant for a period of time and then increases based on the index used by the lender. Lenders use the rates on one-year constant-maturity Treasury -- CMT --- securities, the Cost of Funds Index -- COFI -- and the London Interbank Offered Rate, LIBOR, as an example. Once the interest rate enters the adjustment period, the lender adds a few percentage points -- called the margin -- to the index rate. So if the rate is based on the LIBOR, it might be the LIBOR rate plus 2 percentage points. If the LIBOR is 5 percent at the time, the loan interest rate adjusts to 7 percent. If rates change significantly during the life of the loan, it can cost the investor and cut into his return on investment.

When the interest rate adjusts upward after the fixed period, the investor makes less money unless he also raises his tenant's rent to cover the extra interest expense. But by using IRR calculations, the investor can determine how much the rate can adjust before he needs to raise rent, sell the property or refinance the mortgage at a lower rate. This requires the investor to stay on top of the changes in interest rates and recalculate the IRR, if necessary. The investor with a fixed-rate mortgage doesn't have to worry about interest rate increases.