Question 1157365: Please help, I am really stuck on this question. Thank You!
John and his wife, Nichole, both work and have a combined gross income of $90000 per year. They estimate the property taxes on their condo will be $1150 and insurance would be about $1280 per year. John takes the bus to work, but Nichole has a car payment of $275 per month, and they are both still paying off student loans for a combined total of $285 per month. Use this information to answer the questions below.
Express your answers rounded correctly to the nearest cent!
(i) Determine how much of a monthly mortgage John and Nichole can afford.
Payment = $
(ii) If the couple can get a 30-year mortgage with a fixed rate of 7.1%, use Excel's PV function to determine how much house they could afford
Amount to Borrow = $
Answer by Theo(13342)   (Show Source):
You can put this solution on YOUR website! you are given:
they make 90,000 per year.
property taxes are 1150 per year.
insurance is 1280 per year.
car payments are 275 per month * 12 = 3300 per year.
student loans are 285 per month * 12 = 3420 per year.
i can't say i like this problem, because there are a lot of expenses that are not covered, such as income tax, food, clothing, entertainment, condo maintenance fees, heat, air conditioning, gas, maintenance on the car, transportation costs on the bus, etc.....
the concept, however, should be the same, so you can certainly add these expenses in, if you know what they are, and if you want to.
generally speaking, you take all your revenue and subtract all your expenses and what's left over is what you can afford to spend on a condo.
without taking those into consideration, you would do the following.
90,000 minus 1150 minus 1280 minus 3300 minus 3420 equals net revenue of 80,850.
that's your net income available for payment on your mortgage, not taking into account all those other expenses you have not considered.
80,850 / 12 = 6,737.5.
this is the amount you can spend per month.
if you can get a 30 year mortgage at 7.1% per year, compounded monthly, then you would use excel's PV function to find out how much you can buy a condo for.
the excel PV function is PV(rate,nper,[fv],[type])
here's a reference on how to use that function.
https://support.office.com/en-us/article/pv-function-23879d31-0e02-4321-be01-da16e8168cbd
in excel, you would make the following entries into one of the cells.
=pv(.071/12,30*12,-6371.5)
excel would tell you that the present value = $948,094.79
if you enter the payment as negative, the present value will be positive.
if you enter the payment as positive, the present value will be negative.
if the future value of the mortgage at the end of the investment period is 0, then fv is not required to be entered.
if the payment is made at the end of each month, then the type is not required to be entered.
i did not put these values into the formula above, because they were not required.
you must put the equal sign in front of the function.
if you don't, excel will not recognize the function as something that needs to be performed and you won't get an answer.
in that case, excel will assume that you entered text and will simply show you what you entered, for example: "pv(.071/12,30*12,-6371.5)"
here's a reference on excel formulas you might find useful.
https://exceljet.net/excel-formulas-and-functions
back to how much of your revenue you should consider to be available for paying off your mortgage.
here's a reference:
https://www.interest.com/mortgage/how-much-house-can-you-afford/
using the simpler rule, no more than 28% of your gross monthly income is considered.
if your gross yearly income is 90,000, then divide that by 12 to get a gross monthly income of 7,500.
28% of that is equal to 2,100.
using the excel PV formula, your inputs would be:
=PV(0.071/12,30*12,-2100)
and your present value would be equal to $312,485.14
the present value is the value of the house you can afford if you pay that amount at the end of each month, assuming you didn't put any money up front as a down payment.
i hope this helps.
as i said before, the problem is very unrealistic, because it didn't take into account a large range of expenses that would need to be considered.
the rule of thumb gives a more accurate view of what you can afford.
the reference gives you more information regarding that.
|
|
|
