Great spreadsheet! I really like it. I've made some adjustments to account for multi-family properties and added an additional metric to consider appreciation. So I have an annualized ROI with and without appreciation considered.

Can anyone please share if you received from Jim
olajdia@gmail.com

Jim, I noticed that omission as well. Could you share your updated spreadsheet? I'm at walterrealty@astropheenterprises.com

Same! Please send my way. Mbehr4@Fordham.edu

Hey Jim! spencer@riversrealestate.ca please and thank you!

If you could share also, very much appreciated. Kudos for the work! <sirkako@gmail.com>

Can you share your changes?
https://uploads.disquscdn.c...

it will be highly appreciated if you forward me the same
acc.mhmd1990@gmail.com

Seems like the PMI field is new based on the images above. It's not being divided by 12, so it's causing the income numbers to be way off. Awesome sheet.

Hey FINomad! I've been using this spreadsheet for evaluating all the properties I've bought, so thank you! I noticed that the link on the rents tab is broken now, and I wasn't able to fix it by going to the rentbits.com site and figuring out what the link should be updated to. Is there a fix? Thanks!

Well never mind, I downloaded a new copy and it worked. Disregard!

What did you have to do to fix? Do you have the updated version? Could you share? Mbehr4@Fordham.edu

This is amazing.. Does anyone have an updated version they would be able to share with me? Honzrealty@gmail.com

One more alteration - "Pro Forma CAP Rate" isn't a different calculation than CAP Rate - Pro Forma numbers just mean that the agent or seller is using estimated rents and expenses, not actual rents and expenses, to calculate CAP rates. This is usually used when a unit is newly remodeled then put up for sale, so the past rental numbers are no longer accurite and the agent is estimating what the rental numbers will be moving forward.

Great Sheet - I noticed that you have CAP RATE as $N8/$E4 (Rental Income / Purchase Price), but the correct formula for CAP RATE would be =$N26/$E4 (*Net Income* / Purchase Price).

CAP Rates take into account expenses, while the GRM (gross rent multiplier) is the number you may be thinking of, which takes Purchase Price and divides it by gross rents without accounting for expenses.

Thanks for sharing this, super helpful!

How do I use this spreadsheet if I pay for a property cash? In other words, If I give the seller a check for 100% of the purchase price upfront? (no loans).

zero out the lines for cost in the upper left (or put them at 1$)

What is the thinking behind the "vacancy/placement" numbers on row 18 (eg, cell $E$18)? It seems to the monthly rent, divided by average stay in years. What expense is that signifying?

There is average placement time to get new tenants. Additionally, property management gets the first month rent for any new tenant so you will be out that from the start. So, with 10% default vacancy you will get just over two months every two years that you don't have someone in the apartment. This assumes it only takes one month to place them and then one month taken by the PM. Most of these numbers fluctuate wildly depending on many factors. Some areas may have much less vacancy, others much more.

Wow. I've looked over and used several of these online and this one seems to be amazing. Furthermore, I think it is about to save me on what would have been a pretty bad investment. Actually not sure I could consider it an investment if the ROI and cash flow are negative. I am new to this. Can anyone explain what the Capital Expenditure % is, why 8% how is this calculated?

CapEx is a percentage you set aside for 'capital expenses' which would be things like a new roof, a new fridge, fix siding and so on. 8% is really the bare minimum here and it would be prudent to bump that to 10% if possible (even 12% for an older property)

Love the spreadsheet! Is the CapEx the same idea as Maintenance Repairs that I see in other spreadsheets I've looked at? I'm sure I'm missing it, but I don't see a row under expenses for maintenance repairs/reserves. Unless that's what the Additional Expenses row is for? Sorry I'm brand new to real estate investment and using a spreadsheet like this.

hey, thanks for sharing this - I think it's great! What are your thoughts on changing the formula in cell F3 to this: =($E18*$E19)+E22.

Currently, I don't think the Monthly Income calculation is incorporating "Other Monthly Income" as it should.

Noticed same thing, would be helpful for things like Pet Fees kept by owner. For now, just adding to rent line when applicable. Thanks for this.

Could you share the updated sheet? Mbehr4@Fordham.edu

HI Jim
Can you send it to me as well ? jesus.saenz.morin@gmail.com

Thanks

Really like your spreadsheet!
My husband and I are looking to purchase a vacation home that is also a rental. Are there some alterations we can make to reflect one property revenue assumptions being rented for a 3 days and also in weeks? Would love any help you could provide.
Thank you!

Hi there,
using your spreadsheet to estimate the value of a possible vacation home/rental. How can I reflect the revenue assumptions in days and not years?

This question was asked previously, but the answer didn't make sense, at least not with this latest version of the spreadsheet...
How should you fill this out if you are paying cash for a property? I tried 'zeroing out' several of the fields, but nothing seemed to work properly.

Again, great spreadsheet. Sharing another correction. Someone let me know if I am missing something.

The calculation for Expenses as % of Gross Income should be Total Expenses/Gross Income.

Exactly what I was looking for. Thank you!

Thank you for the spreadsheet.

Thank you very much for the program, Joel. I use it almost daily to verify the quality of each deal which comes across my monitor.

Would you please help me with the following as I have always relied on a work around to get my result? I want to have your program, and the function connect up to the web and pull rent data for studio, 1BD, 2BD, and 3+BD units. My work around is to use Rent-O-Meter which gets old quick because I have to switch from Excel to a browser window to do the search. Want to keep my analysis as much as possible inside your program (a program is supposed to simplify life, right?).

The rents function has never worked for me. In "Entry" worksheet, see cells: C42, D42, and E42. For me they have always reported "#NAME?" Upon un-hiding and studying the "rents" worksheet I see the following function:

"=importjson("https://www.rentdata.org/da..."&Entry!$C2,"fmr3")"

Did the web site RentData block your program's access to the database of rents? I tried to access the website and its data via browser at some point but hit a dead end. Seems like they changed up either the format in which they allow the data to be accessed. Or, the link was changed? Or both?

I have some experience with MS' VBA coding but am unfamiliar with the ImportJSon function, unfortunately. I'm running Win7Pro with MS Office 2013 Pro Plus.

Please help.

-T.A.

Great spreadsheet, I use it all the time. Have you tried to integrate IRR?

Have you given any thought to developing a multi-property portfolio edition of this spreadsheet?

I just downloaded the spreadsheet. TIn addition to the README, Entry, and printable tabs, there is a tab "_RiskSolver_" that appears to have broken references to Sheet 2, and I cant find any explanations in the readme tab. Can you enlighten me please?

In calculating NOI, is vacancy counted twice? Once to get to gross income and then again in total expenses.

I had this questions and then Read your answer further down.

not sure but to my eye you forgot to include the "additional expense" cell in your expenses. In any case, easy enough to fix in my copy, thought I'd let you know.

thanks for the good work!

this is great thanks! question... how did you learn about www.rentdata.org/data/zip?zip

are there other sources of different kinds of data in json format available on the web? how do you find these things?

thank you

Awesome resource, thanks!

I tried it with my current rental and for whatever reason, I'm not getting the same P&I number.
The spreadsheet is saying $1623 P&I but the actual P&I is $1462 (~10% diff).
Any idea what could be off?

Without seeing your exact one I can only guess, but it could be the percentages on the spreadsheet for property management (it is default 10%), you can probably zero it out, just modify your copy of the spreadsheet and zero things out.

I think what's going on is due to refinancing.
User error.
Thanks!

Great table, thank you! It is really helpful.

There is slight error in columns for 10th, 15th, 20th.. year in the "Equity accrued (range)" row. Starting month should be for instance (5*12)+1 and ending (10*12). (not (5*12)+12... and so on).

Anyway, thank you for the spreadsheet!
Cvele

I actually found further errors, so I am pasting the correct formulas for each column below. Oh, I checked the math against my amortization table, and verified said formulas to be accurate. The specific formulas should be updated to:

Year 1: =-CUMPRINC(E12/12,12*E14, (E5-G16), 1, 12, 0)
Year 2: =-CUMPRINC(E12/12,12*E14, (E5-G16), 13, 24, 0)
Year 5: =-CUMPRINC(E12/12,12*E14, (E5-G16), (2*12)+1, (5*12), 0)
Year 10: =-CUMPRINC(E12/12,12*E14, (E5-G16), (5*12)+1, (10*12), 0)
Year 15: =-CUMPRINC(E12/12,12*E14, (E5-G16), (10*12)+1, (15*12), 0)
Year 20: =-CUMPRINC(E12/12,12*E14, (E5-G16), (15*12)+1, (20*12), 0)
Year 30: =-CUMPRINC(E12/12,12*E14, (E5-G16), (20*12)+1, (30*12), 0)

And if it is not clear to anyone, Year 5 is the total for Years 3, 4, and 5. Year 10 is the total for Years 6, 7, 8, 9, and 10. Same goes for Year 15, Year 20, and Year 30.

Such a cool worksheet.

I did notice some conditional formatting issues with my version of Excel Office 365 for the cells for Cash ROI, The 2% Rule, and The 50% Rule. For example, if formatting was set to {Cell value} {greater than} {="20%"}, I had to modify the last part to just {.2}.

I will let you know if I come across anything else. Just getting into the worksheet so I have a lot to play with.

Where can you download this? The download button seem to be illusive and cannot be found anywhere?

Seriously, great work. Was in the process of building a sheet and thought, I know someone has done something similar. Bravo

really nice. thanks for sharing

Hi, This is such a wonderful tool to have. I am starting to play around with it. I'm curious, about how you put together the cap rate formulas. I did some research and it looks like for purchase cap rate you take NOI/Purchase price and for or Pro Forma Cap Rate you take NOI/ARV. Is that what you came up with? Let me know you thoughts