Case study: a rental property portfolio
From: Jordan Mills, Mills Family Properties (a fictional business)
About: Which property should we look at first?
We own eight rental properties across three towns, each with a mortgage. For each one I'd like the annual rent, the net operating income, the cap rate and where it ranks, what the mortgage costs us a year, and the cash it leaves after the mortgage.
Then tell me which property to review first (the lowest cash flow) and the cap rate of the portfolio as a whole. Mortgage terms are on the Inputs sheet.
Property analysis uses a short list of standard measures. Net operating income (NOI) is rent collected minus running costs. The cap rate is NOI divided by the price paid, a quick way to compare properties of different sizes. Cash flow is what is left after the mortgage.
The mortgage comes from PMT, which needs a monthly rate and a number of months when payments are monthly. Two traps wait at the end: ranking needs a locked list, and a portfolio-wide rate comes from dividing totals, not averaging the individual rates.
This is practice with made-up figures, not investment advice.
=I2/C2
=-PMT(Inputs!$B$1/12,Inputs!$B$2*12,G2)*12
Cap rate, and a yearly mortgage cost from a monthly PMT.