# What is the formula for calculating the max value of a home purchase, including monthly property taxes

How can I adjust the present value formula (PV) to account for also having to pay monthly property taxes?

If I know how much monthly payment I can afford (PMT), I can use Excel's `PV` formula to determine how much I can afford to borrow when getting a mortgage. Add your downpayment to that, and you can determine how much house you can afford to buy (excluding transaction fees, closing costs, etc).

If I wanted to include property taxes to this calculation, how would I accurately do it? Assuming we pay it monthly, property taxes would be a significant chunk of your monthly payment, but it is a function of the value we're trying to compute, i.e. the purchase price of the home times the local property tax rate. Thus we only know it after getting our answer. Given the purchase price is what we're calculating, is there a way to mathematically account for this in the input to the calculation?

• Since property tax varies by town, and by assessed condition of the property -- and buying the property may be considered an assessment! -- you have to figure this out for each property individually. Figure out what you're willing to pay for the property. Convert that to a monthly payment. Add property taxes and maintenance and any other recurring costs to get total monthly cost. Decide if that's in a range you can afford. If not, lower how much you're willing to pay and repeat the process until you get a number you like. Then hope you can negotiate the seller down to something in that range. Nov 28, 2022 at 19:11
• Oh -- don't forget that insurance is also an ongoing cost, and can be a very large one if you're in a flood plane or other higher-risk area. And remember utilities, if you aren't used to paying them. Personally I didn't bother trying to calculate down this far. I just decided what my limit was, derated about 10% to allow for those incidentals, budgeted another 10% after purchase for renovation, and waved my hands until I took off. Nov 28, 2022 at 19:15
• There is no one function in Excel that will compute this for you. This is more a question about how to design a spreadsheet than it is about finance. Nov 28, 2022 at 22:52
• I'm not sure there is a clised-form answer; you may just have to iterate/interpolate/approximate. Nov 29, 2022 at 17:14
• Well you could handwave by estimating the total additional costs over how long you'll own the house or the life of the mortgage You might or might not want to adjust that for expected increase in those costs from year to year. Then add that to the purchase cost before trying to calculate the monthly payment. I'm not claiming that will give good results, just that it's a possible approach. Nov 29, 2022 at 18:57

Roughly speaking, property tax is a percentage of the value of the house. Let's say for the sake of argument it's 1% annually - you can look up a rate at SmartAsset (https://smartasset.com/taxes/property-taxes), although this is not the actual rate. You also need home insurance, which will also be more expensive for a more expensive house, and it's harder to look up a rate for this, but let's say another 0.5%. So you need to pay 1.5% of the cost of the house per year.

Now let's make some assumptions:

• you can afford \$2,000/month in mortgage payments
• you're going to get a 30-year mortgage at 7%, with no PMI, 20% down

Then you need the mortgage payment (PV applied to 80% of the value of the house), plus taxes and insurance (1.5% of the full value of the house), to be under \$2,000.

Say your house is worth \$100,000, and you're borrowing \$80,000 (remember 20% down). Then the monthly principal and interest payment is \$532.24 under these assumptions - in Excel, `PMT(0.07/12, 360, 80000)`. The taxes and insurance are \$1500 per year, or \$125 per month - the total is \$657.24.

If you can afford, say, \$2,000 per month, then that gives a target price of 2000/657.24 * 100000 = \$304,301. And we found this without "fiddling" - we only calculated `PMT` once.

The big problem is if you start having to account for PMI, because PMI isn't a constant fraction of the house value.

• Ditto flood insurance. Mine had gotten northwards of \$3000/year before I realized that commercial was cheaper in my area than the government offering. Nov 29, 2022 at 17:16
• Sure, that makes sense - I don't live somewhere where that's an issue, so it didn't occur to me. Nov 29, 2022 at 18:23