Excel formulas PV , FV other

Topic

New Reply

Is there a correct formula to use to evaluate 2 exact properties, Property A that costs say $10,000 but has no HOA fees, and Property B that is exactly the same ie that costs the same amount of $10,000 but has an hoa fee of $500 a year.

For simplicity sake If you estimate that you will own either property for 20 years. is there a way of calculating what financial effect this hoa fee has on the present value of the property.

How much should one lower the value based purely on this $500 yearly outlay, ie how much lower would you offer in price to buy the property with the hoa fee versus the property with no hoa fee….. with all other factors being the same and say the hoa does not change over 20 years just to keep the math simple….
HOA = home owners association

You could also say one property you have to buy a yearly tennis membership and the other property no membership but both are exactly the same and both priced the same. How does one calculate over the ownership period what would be todays proper value with this negative outlay on one property.

Thanks
Jrk

Reply | Quote

Replies

I am not an accountant, but I think it requires more information. It seems to be that it will depend on whether you are borrowing the money and will have loan payments or have the cash and won’t get interest if you spend the money. It will also depend on what you are assuming the interest rate in whatever case you are considering. Also are you doing things on an annual basis or monthly basis…

Steve

Reply | Quote

Thank you, but I don’t want to “overthink” this one.
Just to keep it ultra simple, if one were to choose to make an all cash offer on property b that has a HOA fee of $500 a year and planned to own the property for 15 years how much less would you offer for property b versus property a. Would it be as simple as $500 x 15 years so $7500 less or is there a more elegant approach formula to this.???
Thanks
JK

Reply | Quote

What you’re looking for is the discounted present value (PV) of $500/year for 15 years. You do need to make an assumption about the appropriate rate of interest to discount the cash flows. I’d recommend picking the rate based on what you assume you could earn on the $500/year if you had it rather than having to pay it out. The Excel function for your problem would be =PV(rate,15,500,,0) if the $500 payment was due at the end of each year, or =PV(rate,15,500,,1) if the payment was due at the beginning of each year. The rate is entered as a percentage, for example 3% would be entered as .03.

Reply | Quote

I would hope someone with some accounting skills could confirm or offer another take on it, but I believe the amount you want the initial value to be decreased would be:
=PV(AnnInt,Years,,-FV(AnnInt,Years,-AnnPay))

So for example if the interest rate was 3%:
=PV(3%,15,,-FV(3%,15,-500))
=$5,968.97

so a $10,000 with no fees would be equivalent to $4,031.03(=10,000.00-5,968.97) with $500/year fees.

The simple way you calculate it is equivalent to the formula with an AnnInt of 0%. It is presuming that you would not do anything with the 500 to make any money in the future. Your way is like presuming you would put $500 a year in a box and have $7,500 in the box at the end of the year, as opposed to being able to invest the $500/yr in some account that earned some interest (I presumed 3%).

Steve
PS. Essentially what I was ensuring was that FV of the 10000 with no annual was equivalent to some investment with 500/yr investment:
It comes down to that the value:
=FV(AnnInt,Years,,-InitCost)
is the same as
=FV(AnnInt,Years,-AnnPay,-(InitCost-PV(AnnInt,Years,,-FV(AnnInt,Years,-AnnPay))))

PPS. I emphasize I am not an accountant. I am confident in the math, the question is in the logic, is the right question using the FV in the manner I did and the ensuring those values are equivalent or should it be solved in a different manner…

Reply | Quote

Actually, JK, my formula is the equivalent of Steve’s, and is easier to grasp. PV(3%,15,500) = 5968.97, the same as Steve’s result. When he adds the term, FV=(3%,15,500), that is calculating the future value (at the end of year 15) of 500/year, increased by 3%/year, for 15 years. That future value amount is then discounted back to a present value in his formula, using the same 3% and 15 years. The result is identical to calculating the PV of a 500/year payment for 15 years, using a 3% discount rate.

Reply | Quote

Another way to look it to “confirm” that the calculations make sense is to assume that you are going to borrow the money and pay out the loan at a particular interest rate.

The annual payments on $10,000 are:
=PMT(AnnInt,Years,-InitCost)
=PMT(3%,15,-10000)
=$837.67

Since you are going to be paying an additional $500/year, to be equivalent, you want to have a loan with a payment of $837.67-500 = $337.67. It so happens that the value calculated in Post#5 of this thread ($4,031.03) is the value you would be after:
=PMT(3%,15,-4031.03)
=$337.67

So even with the alternate thought pattern the equivalent amount is calculated the same:
=InitCost – PV(AnnInt,Years,,-FV(AnnInt,Years,-AnnPay))

Of course you will get slightly different results if you assume monthly payments, rather than Annual payments:
=InitCost – PV(AnnInt/12,Years*12,,-FV(AnnInt/12,Years*12,-AnnPay/12))

Steve

Reply | Quote

Thanks. I did not realize I was overcomplicating the formula so much. I suppose that is why I am a chemist and not an accountant…

Steve

Reply | Quote

Thanks. I did not realize I was overcomplicating the formula so much. I suppose that is why I am a chemist and not an accountant… Steve

I am an accountant, but I’m guessing you understand the PV formula math better than I.

Excel’s function reference help should be clearer in describing the FV parameter. FV is an optional term used in the case where there is a separate final cash flow at the end of the entire time frame.

Here’s an example: a real estate investor is considering the purchase of a rental property. He estimates it will generate annual net cash flows of $10,000. He plans to hold the property for 5 years, and expects the net selling price at the end of 5 years to be $200,000. He requires at least a 12% rate of return. What is the maximum amount he can pay? The Excel formula would be:

=PV(12%,5,10000,200000) = -$149,533. PV is negative because to the investor it represents a cash outflow.

Reply | Quote