Published by Ryan Windsor
on 10/09/2025
For newer investors, our clients have told us that the free property portfolio templates they can get online are a waste of time. They’re either too complex for the needs of a landlord with one property or an investor wanting to buy their first one. And they’re often way too simple for more experienced developers wanting to scale up.
With that feedback in mind, we created one for you. This portfolio shows you cash flow, yield, current value and equity by property and across your portfolio.
It’ll take around 5 minutes to set up but it’s worth it. It looks complicated but we wrote this both for Excel novices and experts. Follow it through one step at a time.
Step one: create an Excel or Google Sheet spreadsheet
Start small and simple. Beginning with one tab is more manageable than a multi-tab sheet with interlocking formulas. You can always add greater complexity as time goes on.
Create a new file and name it something you’ll recognise later, like Property portfolio – starter. Open a single tab and call it “My property”. Now, follow these instructions:
Step two: layout your columns
The rows are where you’ll list your properties and the columns are where you’ll store your variables. With any property portfolio, it’s crucial to have the right information to build from.
| Category | Column Name | Column | Description |
| Property Details | Address & Postcode | A | The location of the property |
| Property type | B | Like an HMO, a single let or holiday let | |
| Beds | C | The number of bedrooms (important for rental estimates) (numeral only) | |
| Finance Details | Purchase price | D | How much you paid for the property |
| Deposit % | E | The percentage of the purchase price you paid in cash | |
| Original mortgage | F | The total amount you borrowed from your lender. | |
| Outstanding mortgage | G | How much is left on your mortgage | |
| Rate % | H | The current interest rate on your mortgage | |
| Mortgage term | I | How long your mortgage agreement is for (in years) | |
| Term remaining | J | How long is left on your mortgage (in years) | |
| Repayment/IO (interest-only) | K | The type of mortgage product you have | |
| Monthly payment | L | How much you pay the lender each month. | |
| Rent pcm | M | How much rental income you receive each calendar month | |
| Other income pcm | N | Any extra income from the property, like from a garage | |
| Typical monthly costs | O | How much for insurance, property manager fees, council tax (if applicable) and so on | |
| Net cash flow (per month) | P | The cash you’re left with every month after expenses (before tax) | |
| Gross yield | Q | Annual rent divided by the purchase price | |
| Monthly tax | R | How much is a good idea to put back to pay corporation tax on this property each month. | |
| Key Dates & Management | Gas due, EICR due, HMO licence due | S | These are essential dates to avoid fines |
| Notes | T | Use this column to keep important reminders about the property or your tenants | |
| Next action | U | Write down what you need to do next with these properties or tenants and when by | |
| Equity and value calculators | Estimated property value | V | An estimate of the value of the property since you owned it |
| Equity | W | This is the value of the current property minus the outstanding mortgage |
Step three: added a worked example
Now, after completing the setup, add a worked example. In italics in the descriptions, you’ll see how you need to format that cell. To format a cell, click in it and then right-click. From the pop-up menu, select “Format cells”. From the pop-up menu, select the options in italics. If there is none, you can leave it as it is.
So, in C2, enter the number of beds and format it as in the brackets. For F2 and other cells where there is a formula, enter the formula and then format the cell. (dp=decimal places)
| Column name | Cell | Enter and give this format | Formula – paste this into the cell |
| Address & Postcode | A2 | 12 Acacia Avenue, LS7 3AB | |
| Property type | B2 | Small HMO | |
| Beds | C2 | 4 (Number, 0 dp) | |
| Purchase price | D2 | £180,000 (Currency, £, 0 dp) | |
| Deposit % | E2 | 25% (Percentage, 2 dp) | |
| Original mortgage | F2 | (Currency, £, 0 dp) | =IF(OR(D2=””,E2=””),””, D2*(1-E2)) |
| Outstanding mortgage (today) | G2 | (Currency, £, 0 dp) | =IF($D2=””,””,IF(LEFT(LOWER(TRIM(K2)),1)=”r”,IFERROR(PV(H2/12,MAX(1,J2*12),-L2,0),F2),F2)) |
| Rate % | H2 | 6.00% (Percentage, 2 dp) | |
| Mortgage term (years) | I2 | 25 (Number) | |
| Term remaining (years) | J2 | 25 (Number) | |
| Repayment/IO | K2 | Enter “repay” or “interest” | |
| Monthly payment | L2 | (Currency, £, 0 dp) | =IF(OR(F2=””,H2=””,I2=””,K2=””),””,IF(LEFT(LOWER(TRIM(K2)),1)=”r”,-PMT(H2/12, I2*12, F2),F2*H2/12)) |
| Rent pcm | M2 | £1,200 (Currency, £, 0 dp) | |
| Other income pcm | N2 | £0 (Currency, £, 0 dp) | |
| Typical monthly costs | O2 | £192 (Currency, £, 0 dp) | |
| Net cash flow (per month) | P2 | (Currency, £, 0 dp) | =IF($D2=””,””, IFERROR(M2,0)+IFERROR(N2,0)-IFERROR(L2,0)-IFERROR(O2,0)) |
| Gross yield | Q2 | (Percentage) | =IFERROR( IF(OR(M2=””,D2=””),””, (M2*12)/D2 ), “” ) |
| Monthly tax (25% set-aside) | R2 | (Currency, £, 0 dp) | =IF(OR(M2=””,O2=””,L2=””,F2=””,H2=””,I2=””,K2=””),””, MAX(0, 0.25 * ( M2 + N2 – IF(ISNUMBER(SEARCH(“repay”,K2)), IFERROR(-IPMT(H2/12, MAX(1, ($I2-$J2)*12+1), I2*12, F2), F2*H2/12), L2 ) – O2 ))) |
| Gas/EICR/Licence | S2 | Dates (dd/mm/yyyy) | |
| Notes | T2 | Find professional tenants | |
| Next action | U2 | Call lettings agent | |
| Price inflation (p.a.) | AA1 | Annual house price increase (Percentage) | Put a percentage figure in here like 2% or 3%. Over the last 10 years, the figure has been 5.31%. |
| Estimated property value | V2 | (Currency, £, 0 dp) | =IF($D2=””,””, $D2 * (1 + IFERROR($AA$1,0)) ^ MAX(0, IFERROR($I2-$J2,0))) |
| Equity (today) | W2 | (Currency, £, 0 dp) | =IF($D2=””,””, MAX(0, SUM(V2,-G2) )) |
Step four: get your template ready for your first few properties
Highlight cells F2, G2, L2, P2, Q2, R2, V2 and W2 and drag to row 31. The cells will remain empty. If you want to check they’ve populated other cells, select a cell and look for the formula in the formula bar.
This means the spreadsheet will monitor the first 30 properties in your portfolio.
Step five: an overview on your portfolio performance
Here’s how to keep an eye on your overall property portfolio. Again, after the formula, you’ll see how to format each cell. If there is nothing there, you don’t need to change it:
| U (label) | What this tracks | V (formula to paste) | Format |
| Properties tracked | Count of rows with a purchase price | =COUNTIF(D2:D31,”>0″) | Number |
| Total purchase price (£) | Sum of purchase prices | =SUM(D2:D31) | £ |
| Total loan amount (£) | Sum of original loans | =SUM(F2:F31) | £ |
| Portfolio LTV at purchase (%) | Loans ÷ purchase prices | =IFERROR(SUM(F2:F31)/SUM(D2:D31),0) | % |
| Weighted average interest rate (%) | Rate weighted by original loans | =IFERROR(SUMPRODUCT(F2:F31,H2:H31)/SUM(F2:F31),0) | % |
| Total beds | Sum of bedroom counts | =SUM(C2:C31) | Number |
| Monthly rent in (£) | Rent + other income | =SUM(M2:M31)+SUM(N2:N31) | £ |
| Monthly mortgage out (£) | Sum of monthly payments | =SUM(L2:L31) | £ |
| Monthly running costs out (£) | Sum of typical monthly costs | =SUM(O2:O31) | £ |
| Net cash flow (monthly) (£) | Portfolio total | =SUM(P2:P31) | £ |
| Net cash flow (annual) (£) | Monthly net × 12 | =SUM(P2:P31)*12 | £ |
| Portfolio gross yield (%) | Annual rent ÷ total purchase price | =IFERROR((SUM(M2:M31)*12)/SUM(D2:D31),0) | % |
| Average gross yield (simple, %) | Mean of per-property yields | =IFERROR(AVERAGEIF(Q2:Q31,”>0″),0) | % |
| Monthly tax set-aside (£) | Total of R | =SUM(R2:R31) | £ |
| Estimated annual tax set-aside (£) | Monthly × 12 | =V15*12 | £ |
| Repayment mortgages (count) | Count where K contains “repay” | =COUNTIF(K2:K31,”*repay*”) | Number |
| Interest-only mortgages (count) | Count where K contains “interest” | =COUNTIF(K2:K31,”*interest*”) | Number |
| Average rent per property (£/month) | Rent ÷ properties tracked | =IFERROR(SUM(M2:M31)/MAX(1,COUNTIF(D2:D31,”>0″)),0) | £ |
| Total equity accumulated (£) | Sum of per-property equity today (column W) | =IF(SUM($W$2:$W$31)=0,””,SUM($W$2:$W$31)) | £ |
Step six: you’re ready
You can enter your first property in row 2 and then each new property as you grow on the row below. The template showcases the connection between all of your different properties and how they contribute to your overall portfolio.
Step seven: send it to an expert
Let an expert give your spreadsheet a short review. Their experience will save you time and money as you target the growth of your property business.
HMO Architects provide expert assistance to landlords, developers and investors at every stage. We’ve helped over 750 investors design and realise their projects, securing a 97% planning permission rate. In total, our clients’ projects now top £100m in value.
You’ve got a number of choices:
- Mortgage calculator: Calculate your monthly instalments for both full repayment and interest-only mortgages.Whether you’re considering an HMO, serviced accommodation, or a rental property, it provides a tailored breakdown of monthly payments, interest costs, and long-term returns.
- Deal analyser calculator: Check online if your deal is worthwhile to pursue. From construction costs to VAT, this tool breaks down every detail, so you can make informed decisions without guesswork. Enter your figures, and the Deal Analyser will calculate your total investment, including VAT.
- Investment strategy call: Get tailored advice on how to build and scale your property portfolio from Ryan Windsor. Ryan has worked on over 2,200 property projects. He began building his own extensive property portfolio. Share your ideas on growing a portfolio with him and get tailored guidance on a discovery call. [Related article: Ryan Windsor’s BRRR Method in Property]
- Feasibility study: Speak to Giovanni Patania, our Architect Director, about pre-buy and design feasibility studies. Know what you need to do to provide an HMO your target market wants while preserving and growing your yield.
View our range of development finance case studies to see how we’ve helped clients turn their ideas into sound investments. Read customer stories on HMO, flat, holiday let and housing projects.
Call us on 01223 776 997 or email us directly.
Published by Ryan Windsor
on 10/09/2025
Ryan Windsor, Development Director and co-founder of HMO Architect, brings over 15 years of specialised experience in HMO development to the table. Having consulted on nearly 2,200 projects, Ryan is a highly seasoned HMO landlord with a vast and influential property network. He began his real estate journey at just 17, rapidly amassing a wealth of experience that sets him apart in the industry. Beyond his professional successes, Ryan is passionately dedicated to giving back, leading numerous charitable initiatives that make a meaningful impact on local communities.
