Please check / rate my excel spreadsheet

[R377]

Hi,

I was looking around everywhere for an excel spreadsheet for my record keeping but just could not find one that I liked so I made my own. Please note I have a separate one for all property expenses to give my accountant at tax time but just wanted something with all my overall details/net worth of each property and total portfolio to be included with my will.

If you could please check it out and have a play with it that would great. Any errors or suggestions would be appreciated thanks.

 

[D.T.]

The first thing I noticed was column M is a complete waste of space. You're using it just to be able to refer to the right hand columns whilst scrolled across when you could just lock column B to achieve the same.

It also looks like you're using P&I interest instead of IO for your loans?

What are you actually trying to work out? It isnt very clear.

 

[R377]

Thanks for having a look. I am pretty sure that it is calculated as interest only.

It was designed so i can keep track of my portfolio but also give the correct details and net worth of my properties should I die.

I wanted it basic but also have alot of the key info hence column M so it easy to view for my parents as they don't know alot about investing and computers.

Rather than them just sell off all my properties it would be nice for them to see an appreciate the ongoing profits you can make by just holding the properties. eg. rent going up, equity

 

[Joel]

Regards to column M, my understanding is that it is there so you want to see what the assets are in which is in column B but there is insufficient screen real estate.

I suggest using the freeze panes function so that column B is always in view.

Brief instructions for Excel 2013 below, other versions of Excel should be similar:

1. Click on cell C2
2. In the ribbon menu up top click on View
3. Click on Freeze panes

 

[R377]

Thanks for trying to help Joel, i have 2003 or something old and basic so i cannot find the freeze frame.

 

[Joel]

Thanks for trying to help Joel, i have 2003 or something old and basic so i cannot find the freeze frame.

I know freeze panes is a very old Excel feature. If you don't have the pretty ribbon menu up top, I'm sure google has the answer. It is probably buried in the View menu.

 

[Lacasa]

I don't mind the spreadsheet. I would add something to show expenses to be deducted from column L i.e. cost of property management etc.

Not sure why you would want the total sum on column M and N.

Cheers
Lacasa

 

[Peter_Tersteeg]

For a spreadsheet intended to keep track of IP expenses, the only expense it's considering is interest.

It's also calculating the expected interest, not the actual interest charged, so you can't use this for accounting purposes. Likewise it's calculating the rent, not stating the actual rent received. These can vary from one month to the next.

This spreadsheet looks more like a portfolio summary for finance purposes, not for accounting purposes. We use spreadsheets like this for clients to give an easy way to view summary of their portfolio.



For accounting purposes, a better approach might be to have a page per property on which you can list both income and expenses and manually enter it. This would give your accountant an accurate picture of what actually happened.

This could then feed into a summary page to give you an overall view of the portfolio.

 

[D_One]

One thing that jumped at me was your interest cost. Because you have used a negative nbet for the loan, and a negative number for the total repayments. Although your formula is correct for interst paid being total repayments - principal, the problem is you have two negatives. -1-2 =-3, but I think what you are trying to achieve should have been 1-3=-2.

So my point being, change the principal to a positive number, or change your formula to g+a to attain the same result.