Accounting on Excel, what am I missing?

12 Replies

I have been looking everywhere for an excel template for keeping track of my expenses, income, and reflecting monthly cash-flow for my SFR investment but have not found anything that suits what I'm looking for, so I decided to start my own. Disclaimer: I have no idea what I'm doing. With that out of the way, these are my questions based on the columns I have.

Income:

I have my rent as my only income (plus late fees if any). Is there anything I'm missing?

Expenses: (each of the following is a column)

Mortgage - same month per month

Taxes - same month per month

Insurance - same month per month

Other Expenses - repairs that come up

Reserves - Amount I put aside every month for repairs and save up for capital expenses (roof, windows, etc.)

I don't have a "management" column since I am managing it myself. 

I have found in spreadsheets that "Principal" is NOT considered an expense. Why?! Also, if principal is not considered an expense and interest is, am I supposed to have a column for principal and one for interest and then go in there every month and input it? I'm assuming it would basically look like an amortization table if I do this. 

Deposit

I don't know where to, or if I should, put the Deposit I received.  - Since I can't really spend it, this is not income at all, correct?

My goal is to see an exact amount of money that I can either use to spend (not likely) or put back into my investing account to save up for the next properties. Any in put would help. Thanks!

No, principal is not an expense. I can see why that would be confusing since you are paying it out every month, but here are some things to consider to help you understand why:

1) Imagine you bought a property for cash; should you be able to deduct the entire price as a business expense? Of course not. The principal you are paying is just the same thing, only in increments.

2) And just to add confusion to that (...evil laugh...)  you actually DO deduct (most of) the price of the property over time by taking the depreciation deduction. And obviously, you can't deduct the same thing more than once.

And you're right about the deposits, they are neither income nor expense

Thank you @Jean Bolger  , but how should I categorize it? It's not an expense, but it for sure is not coming into my pocket and staying there month per month.

I see that you're a musician! I myself play trumpet and have a degree in music education, which is what I do full-time. I also play weekends and now added real estate to my plate. Good to see other fellow musicians doing this!

You could have two separate areas on your spreadsheet: One for tax expenses and a cash flow calculator that accounts for principal. 

This is where double entry accounting becomes useful (albeit still confusing at first).

Your mortgage payment would increase your 'owners equity' account and decrease your mortgage balance.

@Aroldo Villarreal  

look for templates that good RE CPA would use for client tax reporting.

I had one that a CPA (we used for tax purposes) structured for us. But it basically followed the itemized Schedule E for rental expenses & the Sched C for the business aspects.

However it was functional & kept everything in 'order'.

But as we evolved we had a lot of notes etc out there so I had to make adjustments to the format.

Aroldo,

Let me first disclaim that I do not own an investment property currently, but I am on the hunt and have conducted quite a bit of research, interviews, etc. including the build up of an Excel model to calculate potential returns, cash flows, taxes, etc. under various scenarios.

With that being said, I am an accountant and can answer several of the questions you posted:

1. Income: Application fees, reimbursements for background/credit checks (if applicable), any additional "fees" whether paid upfront or at the end of a lease (pet fees, cleaning fees, smoking fees). Generally, any payment you receive from a tenant (or even potential tenant - e.g., application fee received from an applicant, but applicant is denied - the fee is still income) outside of the deposit is income.

2. Expenses: I would model "vacancy expenses" at least for the first month you own the property, unless you have a tenant lined up. This would include utilities (albeit at an amount below average given that there are no occupants), advertising and lawn care/snow removal to name a few. You might incur some legal expenses depending on your situation (using some type of legal entity to run your investment through). Also consider the expenses you will incur yourself that are directly related to managing the property - includes basically everything except your TIME.

3. Mortgage principal: You are correct. The repayment of principal to the lender is not an expense. However, you should definitely consider it as a cash outflow when calculating your cash flows and returns. The deductible expense is the interest associated with the mortgage, so I recommend creating an Excel spreadsheet that amortizes your loan and you can link the interest component to your calculation of taxes (Excel has a formula that will build an amortization table very easily). I can send you an example of an amortization table that will allow you to link to your spreadsheets without inputting manually. As for the why - you don't get an expense for returning capital to the original party that lent it to you. Think about it this way, you don't record as income the amount the bank loans to you when you buy a house, so you don't get to deduct the amount you pay back. You only get to deduct the "cost" of that money - interest.

4. Deposit: Correct. This is not income. However, I am not sure how you would treat the deposit at the end of the lease if you withheld it due to damage to your property. My thinking is that it would be included in income since you could deduct the repairs that were being made to the property using those funds. Anyone have a more definitive answer?

In my model I have two main spreadsheets: (1) cash flows so that I can calculate returns, plan for the future, etc. and (2) income and expenses as defined by the IRS in order to calculate taxes, which will then flow to the cash flow sheet. Google IRS Schedule E and you will get a good idea on how to calculate your tax exposure. This will also make sure you pick up depreciation.

Good luck!

OK I really should have said- it IS an expense, it's just not a deductible expense for tax purposes. If you are calculating you cash flow then yes, you would need to subtract it along with other all the other expenses.

I've met quite a few musicians on BP! Real estate is a great way for us to build some stability for the future- I try to talk my musician friends here into it, but most don't listen..

This is both on and off topic... I love excel and most people do not know about this function:

If you're doing a month by month excel sheet you can show the amount of the principal and the amount of the interest paid without an amortization table

Principal: Function is "=CUMPRINC()"

Interest: Function is "=CUMIPMT()"

You can then specify the # of periods and the start and stop time. That way you know how much interested you paid and in what months. It can be a bit confusing until you play around with it. 

Aroldo, I have an Excel sheet that does all of that. I'd be happy to send it to you and walk you through it. We are a music family as well (my wife just earned her PhD in Music Education, and I have a composition degree) that recently started in real estate (2012).

How do we message each other? I'm new to this interface. Can someone help us connect?

Dan

I can send you an example of an amortization table that will allow you to link to your spreadsheets without inputting manually. 

Yes, could you please? Thank you in advance!

@Dan Schwartz  I'll see if I can message you through BP. Yes, I would greatly appreciate the spreadsheet! I didn't see you message until now because I was typing the other one. 

Join the Largest Real Estate Investing Community

Basic membership is free, forever.