How do I input functions to solve for the missing values in yellow?

Picking a Price Sam, the youngest of four, will graduate in industrial engineering this June. His future plans have not solidified yet, but his parents clearly believe that he will be self-supporting. In fact, they are planning on selling their home, taking a world cruise, and investing for their retirement. Sam has been asked by his parents to spend part of his semester break/holidays helping them analyze a 4- plex that they are considering buying. The building is part of a rental complex with cooperative management of the pool and parking areas. The complex is about 5 years old, and it appears stable and desirable. Sam realizes that other investments might be more appropriate, but they are not interested in his general advice. Rather, they have asked him to calculate the highest price that they could afford to offer for the 4- plex and still meet their financial goals (the asking price is $160,000). Also, they have asked him to ignore the impact of inflation and taxes for this preliminary financial analysis since their future financial position and the future tax laws are both unclear. They have developed some information, but they suspect some may be missing. Since they are gone for the evening, and Sam wants to ski tomorrow, he plans on "guesstimating" any missing numbers. This will give his parents a preliminary estimate, and it will involve them in the iterations to a sufficiently accurate answer. Financing for the purchase will come in two pieces. The 20% down payment will be part of the proceeds from the sale of their home, while the other 80% will be financed with a 9%, 20-year mortgage. In discussing this interest rate, his parents also mentioned that their long-term investments in the stock market had averaged an annual rate of return of about 11%. The annual operating costs for the 4-plex, as reported by the current owner, have been about $350 for water and sewer, $150 for lawn mowing, and a $700 assessment from the cooperative pool/parking authority. The renters pay for their own electricity and natural gas. Property taxes are calculated at 1.8% of the assessed value, and properties are assessed at 100% of market prices with biannual adjustments by the city. The city currently appraises the property at $103,000 for the building and $41,000 for the land. Insurance for fire and liability is 1% of the building's value. Rents for these and other similar units in the cooperative have been fairly stable at $550/month or $500/month for long-term leases. Suggestions to the Student 1. Real estate deals usually have substantial transaction costs. 2. Realtor's fees average 6% and are paid by the seller. Loan origination fees, title insurance fees, etc., will often cost the buyer 1.5%. These closing costs must be apportioned between the buyer and the seller-for the property purchase and for its later sale. How much occurs now and how much at the problem's horizon? 3. What horizon should be used? What happens to the property at the problems horizon? 4. How should the property's value at the horizon be determined? What is it? Pick a Price 20.0% Down payment Recommended Purchase Price 80.0% Financed 9.0% Finance rate Use Goal Seek function, settting PW = 0 20 Years Financed for 30 Horizon (years) MARR $350 Water & sewer $150 Lawn mowing Data Block $700 COOP & parking fees 1.8% Property tax rate $103,000 Building appraisal $41,000 Land appraisal 1.0% Insurance rate $525 Average rental (monthly/unit) Finacial Model - 4 Number of units Calculations 80.0% Occupancy rate 1.0% O&M (% of purchase price) 10.0% Management fees (% of annual income) 6.0% Realtor's fees 1.0% Closing costs 1.0% Closing costs Depreciation If House If Equip SL MACRS 6 Fin Cost O&M Cost Ins & Tax Fees Revenue Fin Cost Interest Principle Cash Flow Check If Equip $0 -$7501 -$1,500 $0 $0 $0 $0 $0 Year 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 30 30 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $1,907.41 ($2,500.00) $3,814.81 ($5,000.00) $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $1,907.41 New Cash Flow If house $0 $572 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $572 $0 $0 $0 $0 $0 $0 $0 $0 $0 #NUM! Realtor Fees Copy Home Sale price here: PW IRR Add ALL year 30 rev/exp in single cell here IRR #NUM! Lot More Cash Increased CASH is due to Can only depreciate a HOUSE over 27 years SL. Can depreciate equipment in this example over 15 years using MARCS Picking a Price Sam, the youngest of four, will graduate in industrial engineering this June. His future plans have not solidified yet, but his parents clearly believe that he will be self-supporting. In fact, they are planning on selling their home, taking a world cruise, and investing for their retirement. Sam has been asked by his parents to spend part of his semester break/holidays helping them analyze a 4- plex that they are considering buying. The building is part of a rental complex with cooperative management of the pool and parking areas. The complex is about 5 years old, and it appears stable and desirable. Sam realizes that other investments might be more appropriate, but they are not interested in his general advice. Rather, they have asked him to calculate the highest price that they could afford to offer for the 4- plex and still meet their financial goals (the asking price is $160,000). Also, they have asked him to ignore the impact of inflation and taxes for this preliminary financial analysis since their future financial position and the future tax laws are both unclear. They have developed some information, but they suspect some may be missing. Since they are gone for the evening, and Sam wants to ski tomorrow, he plans on "guesstimating" any missing numbers. This will give his parents a preliminary estimate, and it will involve them in the iterations to a sufficiently accurate answer. Financing for the purchase will come in two pieces. The 20% down payment will be part of the proceeds from the sale of their home, while the other 80% will be financed with a 9%, 20-year mortgage. In discussing this interest rate, his parents also mentioned that their long-term investments in the stock market had averaged an annual rate of return of about 11%. The annual operating costs for the 4-plex, as reported by the current owner, have been about $350 for water and sewer, $150 for lawn mowing, and a $700 assessment from the cooperative pool/parking authority. The renters pay for their own electricity and natural gas. Property taxes are calculated at 1.8% of the assessed value, and properties are assessed at 100% of market prices with biannual adjustments by the city. The city currently appraises the property at $103,000 for the building and $41,000 for the land. Insurance for fire and liability is 1% of the building's value. Rents for these and other similar units in the cooperative have been fairly stable at $550/month or $500/month for long-term leases. Suggestions to the Student 1. Real estate deals usually have substantial transaction costs. 2. Realtor's fees average 6% and are paid by the seller. Loan origination fees, title insurance fees, etc., will often cost the buyer 1.5%. These closing costs must be apportioned between the buyer and the seller-for the property purchase and for its later sale. How much occurs now and how much at the problem's horizon? 3. What horizon should be used? What happens to the property at the problems horizon? 4. How should the property's value at the horizon be determined? What is it? Pick a Price 20.0% Down payment Recommended Purchase Price 80.0% Financed 9.0% Finance rate Use Goal Seek function, settting PW = 0 20 Years Financed for 30 Horizon (years) MARR $350 Water & sewer $150 Lawn mowing Data Block $700 COOP & parking fees 1.8% Property tax rate $103,000 Building appraisal $41,000 Land appraisal 1.0% Insurance rate $525 Average rental (monthly/unit) Finacial Model - 4 Number of units Calculations 80.0% Occupancy rate 1.0% O&M (% of purchase price) 10.0% Management fees (% of annual income) 6.0% Realtor's fees 1.0% Closing costs 1.0% Closing costs Depreciation If House If Equip SL MACRS 6 Fin Cost O&M Cost Ins & Tax Fees Revenue Fin Cost Interest Principle Cash Flow Check If Equip $0 -$7501 -$1,500 $0 $0 $0 $0 $0 Year 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 30 30 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $1,907.41 ($2,500.00) $3,814.81 ($5,000.00) $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $0.00 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $3,814.81 $1,907.41 New Cash Flow If house $0 $572 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $1,144 $572 $0 $0 $0 $0 $0 $0 $0 $0 $0 #NUM! Realtor Fees Copy Home Sale price here: PW IRR Add ALL year 30 rev/exp in single cell here IRR #NUM! Lot More Cash Increased CASH is due to Can only depreciate a HOUSE over 27 years SL. Can depreciate equipment in this example over 15 years using MARCS