Investor · Lansdowne, PA · Member since 2016 · 161 posts · 62 votes
While I've heard people discuss projecting the cost of replacing certain systems over time for rental properties, I haven't so far seen an actual calculator for that purpose. Here's one I just put together in Google Sheets: https://drive.google.com/open?id=1XfrOEQ1Olcl-DiP-...
By and large, I think it will be self explanatory, but I'll stop to explain the formula driving all the columns on the right. Each column is a year in the life of the property, starting from your purchase, and each cell is calculated thusly:
"if the years remaining in the system's life are less than the years we've owned it, then divide the replacement cost by the years remaining in the system's life; otherwise, divide the replacement cost by the total life expectancy"
That way we project worst-case-scenario of having to deduct the entire replacement cost of, say, a roof with only 5 years left in it from the cash flow over those 5 years. And after that, we deduct only the best-case scenario of the system surviving its full life expectancy.
I welcome all criticism, improvements, and general harassment.
This is a better version of something I put together a while back. The pros hammered me on one point that I'll share with you: inflation. The cost to replace that roof now is not the same as replacing that same roof 10 years from now....
Either way, putting this much thought into budgeting is going to protect you when those big ticket items come due. Great job treating the investing like a business an not a hobby.
@Max T. Good point. I added an inflation factor. I hadn't done it for my own purposes because I built inflation into the final numbers for rents and expenses, but I can see how it would be valuable here.
While I've heard people discuss projecting the cost of replacing certain systems over time for rental properties, I haven't so far seen an actual calculator for that purpose. Here's one I just put together in Google Sheets: https://drive.google.com/open?id=1XfrOEQ1Olcl-DiP-...
By and large, I think it will be self explanatory, but I'll stop to explain the formula driving all the columns on the right. Each column is a year in the life of the property, starting from your purchase, and each cell is calculated thusly:
"if the years remaining in the system's life are less than the years we've owned it, then divide the replacement cost by the years remaining in the system's life; otherwise, divide the replacement cost by the total life expectancy"
That way we project worst-case-scenario of having to deduct the entire replacement cost of, say, a roof with only 5 years left in it from the cash flow over those 5 years. And after that, we deduct only the best-case scenario of the system surviving its full life expectancy.
I welcome all criticism, improvements, and general harassment.
Thanks,
Chaz
Thanks Chaz! LOL I took a spreadsheet from the file share and altered it to do the same calculation as you did to determine the life remaining. Thanks again for including the inflation. That was a problem for me to figure out how to calculate. I see you used fv (future value) formula.