Help please, thank you.

Estimating Land Values Below is an example of a flyer from a Story County land auction a few years ago. In this lab, you will work through how much your team be willing to bid on parcel 4 (circled in the picture) Land Information L-1300709 Location Here is the information from From Gilbert, go south on R-50 to County Blacktop E-26, then west 1.5 miles, or from the north side of Ames, go west on George Washington Carver Avenue from State Highway #69 to E-26, then west 1.5 miles. Properties are located on the north and south side of E-26. Please watch for auction signs the flyer: Legal The following are the Legal Descriptions, Average CSR and Property Taxes of the subject properties Parcel 4 (70 Acres) - The Southwest Quarter of the Northeast Quarter (SW 4 Parcel #1-(40 Acres) -The Southeast Quarter of the Northwest Fractional Quarter (SE14 NW Fri. 1/4) of 19-84-24. Average CSR-83.3, County Average CSR-77.6, Crop Acres-374Property Taxes-S1092/yr 04 $ os50 TRACT 4 TRACT# Parcel #2-(56 Acres)-The East One-Half of the Northcast Quarter of the South- west Fractional Quarter (E1/2 NEI/4 SW Frl. 1/4); Northwest Quarter of the Southcast Quarter, Except the East 330' of the North 400' (Nwi/4 SE1/4 Exc. E. 330 of the N.400') of 19-84-24. Average CSR-81.2, County Average CSR-77.6 Crop Acres-55.24, Property Taxes-S1537/yr NE ; Northwest Quarter of the Northeast Quarter 5-4 TRACT #2 Parcel #3-(100 Acres)-The Northwest Quarter of the Southwest Fractional Quar ter, Except beginning at the NE comer, thence West 211.5', South 425', East 211.5, North 425"' to POB (NWI/4 SW Frl. 1/4, Exc. POB NE comer, W. 211.5', S. 425, E. 211.5, N. 425' to POB); West One-Half of the Northeast Quarter of the Southwest Fractional Quarter (W1/2 NEI/4 SW Fri.1/4); East One-Half of the Southwest Quarter of the Southwest Fractional Quarter (E1/2 SW1/4 SW Fri. 1/4); West One-Half of the Southcast Quarter of the Southwest Fractional Quarter (W1/2 SEI/4 SW Fd.1/4) of 19-84-24. Average CSR-80.4, County Average CSR-77.6, Crop Acres-99, Property Taxes-$2795/yr 2s0f gEO 1600 Except the East 10 Acres TRACT # (NW NE 1/4, Exc. E 10 - A) of 19-84-24. Average CSR 84.1, County Average CSR 77.6, Crop Acres - Farcel #4-(70 Acres)-The Southwest Quarter of the Northcast Quarter (SW1/ NEI/4); Northwest Quarter of the Notheast Quarter, Except the East 10 Acres (NWI/4 NEI/4, Exc. E. 10A) of 19-84-24. Average CSR-84.1, County Average CSR-77.6, Crop Acres-63, Property Taxes-S1894/yr9 o2 ele@970 ESA Information 63, Property Taxes - $1,750/yr. on the entire parcel The FSA information on the entire parcel is as follows: Cropland Acres Com Base Yield 134 Bean Base 117.30 Yield Parcel #1-2-3-4 252.97 131.4 34 If parcels are sold separately, the FSA office will reconstitute the subject proper- ties. The Wetlands determination is not complete at this time. The 266 acres are considered NHEL 10 % 7% 8 % . 5 % 4% 6% 9% 11 % Years Assume you are pre-approved for a 30-year loan at 7% interest. You estimate this land will appreciate an average of 4% per year 15 0.08994 0.09634 0.10296 0.10979 0.13147 0.11683 0.12406 0.1390 0.12782 0.12466 0.12193 0.11955 011746 0.11562 0.09227 0.09895 0.10586 0.11298 0.1355 16 0.08582 0.12030 17 0.08220 18 0.07899 19 007614 20 0.07358 0.07128 0.08870 0.09544 0.10243 0.10963 0.10670 0.11705 0.11421 0.1324 0.1298 0.08555 0.08275 0.08024 0.07800 0.09236 0.09941 0.09675 0.09439 0.09229 0.09041 0.10413 0.11173 0.08962 0.1275 0.08718 0.08500 0.10955 0.10762 0.10590 0.10185 O.1255 O.1236 21 0.09983 0.09803 0.07597 0.08305 0.11401 22 0.06920 0.1223 0.08128 0.11257 0.11130 0.11017 0.10606 0.10369 23 0.09642 0.1209 0.07414 0.08871 0.10438 0.08731 0.06559 24 25 30 0.10302 0.07247 0.1197 0.07968 0.08719 0.09498 0.07095 0.09368 0.10181 0.1187 0.06401 0.07823 0.08581 0.05783 0.05358 0.1150 0.06505 0.08059 0.08883 0.07265 0.09734 0.07723 0.07501 0.08580 0.1129 0.06107 0.06897 0.09464 35 0.09296 0.05828 0.1117 0.05052 0.06646 0.08386 0.10226 40 1. Estimate the Net Returns Corn Soybeans Average Yield (10 year Story County average 165 62 Price (10 year average IA cash price) $4.59 $10.33 Gross Income (price x yield) Expenses Seed, fertilizer, etc $240 $155 Values from Records and Estimated Costs of Crop Machinery ownership 50 45 Production (on Ag Decisio Maker) Machinery operating 35 36 Drying, hauling, handling 72 6 Labor 25 Total nonland costs Property taxes (per acre) Total Costs Net return to land 1. Figure the Capitalized Land Value Assume your opportunity cost of capital is 7%. You expect net returns to land to increase on average 4% per year. What is your discount rate (capitalization rate)? 29 a. Opportunity cost of capital - anticipated growth rate b. Land Value (V) Expected Net Return to Land discount rate c. Adjust for Tillable Acres: crop Acres /total acres Land Value (adjustment for tillable acres) 2. What other factors might you consider (or what assumptions about the above analysis might you change) in making your final decision about how much you are willing to bid on Parcel 4? 3. Can you make it cash flow? a. Suppose your lender requires 30% down payment. How much would a 30% down payment be for all 70 acres? b. Calculate your annual payment on the loan for a 70% loan (30% down payment). Then compute the expected surplus or deficit PER ACRE Sale price per acre - Down payment (30%) = Loan amount (70%) x Amortization factor (30 years @ 7 %) = Annual payment Income Available (Adjusted net return page x 0.90) net return from previous (Income payment) Surplus/Deficit c. Does it cash flow? d. If not, how might you address the cash flow deficit? e. What is the maximum loan that will cash flow (per acre)? [Note: you are likely more familiar with asking what the annual (or monthly) payment will be for a particular loan amount. Here you are asking a different question: given the cash flow (maximum payment), how much can I borrow?] Expected Net Return per acre $ % tillable acres Expected Return per acre adiusted for tillable = $ acres (amortization factor) = S Maximum loan per acre that will cash flow f. Given your projected land value, how much down payment will you need per acre? (Your land value minus your maximum loan per acre from above) g. For a loan that will cash flow, how much is this down payment for the total 70 acres? 4. Construct the first 4 years of the amortization table for WHOLE PARCELL AND TOTAL LOAN using the table below. For the beginning principal balance, use 70 times the per acre "Loan amount (70%)" from your answer to 3b. (Answer 3b x 70 Acres = Total Loan) Principal Paid Year Beginning Balance Interest Paid Ending Balance ayment 1 2 3 4 a. How much of the loan principal have you paid down by the end of year 4? What percent of the original principal amount does this account for? Estimating Land Values Below is an example of a flyer from a Story County land auction a few years ago. In this lab, you will work through how much your team be willing to bid on parcel 4 (circled in the picture) Land Information L-1300709 Location Here is the information from From Gilbert, go south on R-50 to County Blacktop E-26, then west 1.5 miles, or from the north side of Ames, go west on George Washington Carver Avenue from State Highway #69 to E-26, then west 1.5 miles. Properties are located on the north and south side of E-26. Please watch for auction signs the flyer: Legal The following are the Legal Descriptions, Average CSR and Property Taxes of the subject properties Parcel 4 (70 Acres) - The Southwest Quarter of the Northeast Quarter (SW 4 Parcel #1-(40 Acres) -The Southeast Quarter of the Northwest Fractional Quarter (SE14 NW Fri. 1/4) of 19-84-24. Average CSR-83.3, County Average CSR-77.6, Crop Acres-374Property Taxes-S1092/yr 04 $ os50 TRACT 4 TRACT# Parcel #2-(56 Acres)-The East One-Half of the Northcast Quarter of the South- west Fractional Quarter (E1/2 NEI/4 SW Frl. 1/4); Northwest Quarter of the Southcast Quarter, Except the East 330' of the North 400' (Nwi/4 SE1/4 Exc. E. 330 of the N.400') of 19-84-24. Average CSR-81.2, County Average CSR-77.6 Crop Acres-55.24, Property Taxes-S1537/yr NE ; Northwest Quarter of the Northeast Quarter 5-4 TRACT #2 Parcel #3-(100 Acres)-The Northwest Quarter of the Southwest Fractional Quar ter, Except beginning at the NE comer, thence West 211.5', South 425', East 211.5, North 425"' to POB (NWI/4 SW Frl. 1/4, Exc. POB NE comer, W. 211.5', S. 425, E. 211.5, N. 425' to POB); West One-Half of the Northeast Quarter of the Southwest Fractional Quarter (W1/2 NEI/4 SW Fri.1/4); East One-Half of the Southwest Quarter of the Southwest Fractional Quarter (E1/2 SW1/4 SW Fri. 1/4); West One-Half of the Southcast Quarter of the Southwest Fractional Quarter (W1/2 SEI/4 SW Fd.1/4) of 19-84-24. Average CSR-80.4, County Average CSR-77.6, Crop Acres-99, Property Taxes-$2795/yr 2s0f gEO 1600 Except the East 10 Acres TRACT # (NW NE 1/4, Exc. E 10 - A) of 19-84-24. Average CSR 84.1, County Average CSR 77.6, Crop Acres - Farcel #4-(70 Acres)-The Southwest Quarter of the Northcast Quarter (SW1/ NEI/4); Northwest Quarter of the Notheast Quarter, Except the East 10 Acres (NWI/4 NEI/4, Exc. E. 10A) of 19-84-24. Average CSR-84.1, County Average CSR-77.6, Crop Acres-63, Property Taxes-S1894/yr9 o2 ele@970 ESA Information 63, Property Taxes - $1,750/yr. on the entire parcel The FSA information on the entire parcel is as follows: Cropland Acres Com Base Yield 134 Bean Base 117.30 Yield Parcel #1-2-3-4 252.97 131.4 34 If parcels are sold separately, the FSA office will reconstitute the subject proper- ties. The Wetlands determination is not complete at this time. The 266 acres are considered NHEL 10 % 7% 8 % . 5 % 4% 6% 9% 11 % Years Assume you are pre-approved for a 30-year loan at 7% interest. You estimate this land will appreciate an average of 4% per year 15 0.08994 0.09634 0.10296 0.10979 0.13147 0.11683 0.12406 0.1390 0.12782 0.12466 0.12193 0.11955 011746 0.11562 0.09227 0.09895 0.10586 0.11298 0.1355 16 0.08582 0.12030 17 0.08220 18 0.07899 19 007614 20 0.07358 0.07128 0.08870 0.09544 0.10243 0.10963 0.10670 0.11705 0.11421 0.1324 0.1298 0.08555 0.08275 0.08024 0.07800 0.09236 0.09941 0.09675 0.09439 0.09229 0.09041 0.10413 0.11173 0.08962 0.1275 0.08718 0.08500 0.10955 0.10762 0.10590 0.10185 O.1255 O.1236 21 0.09983 0.09803 0.07597 0.08305 0.11401 22 0.06920 0.1223 0.08128 0.11257 0.11130 0.11017 0.10606 0.10369 23 0.09642 0.1209 0.07414 0.08871 0.10438 0.08731 0.06559 24 25 30 0.10302 0.07247 0.1197 0.07968 0.08719 0.09498 0.07095 0.09368 0.10181 0.1187 0.06401 0.07823 0.08581 0.05783 0.05358 0.1150 0.06505 0.08059 0.08883 0.07265 0.09734 0.07723 0.07501 0.08580 0.1129 0.06107 0.06897 0.09464 35 0.09296 0.05828 0.1117 0.05052 0.06646 0.08386 0.10226 40 1. Estimate the Net Returns Corn Soybeans Average Yield (10 year Story County average 165 62 Price (10 year average IA cash price) $4.59 $10.33 Gross Income (price x yield) Expenses Seed, fertilizer, etc $240 $155 Values from Records and Estimated Costs of Crop Machinery ownership 50 45 Production (on Ag Decisio Maker) Machinery operating 35 36 Drying, hauling, handling 72 6 Labor 25 Total nonland costs Property taxes (per acre) Total Costs Net return to land 1. Figure the Capitalized Land Value Assume your opportunity cost of capital is 7%. You expect net returns to land to increase on average 4% per year. What is your discount rate (capitalization rate)? 29 a. Opportunity cost of capital - anticipated growth rate b. Land Value (V) Expected Net Return to Land discount rate c. Adjust for Tillable Acres: crop Acres /total acres Land Value (adjustment for tillable acres) 2. What other factors might you consider (or what assumptions about the above analysis might you change) in making your final decision about how much you are willing to bid on Parcel 4? 3. Can you make it cash flow? a. Suppose your lender requires 30% down payment. How much would a 30% down payment be for all 70 acres? b. Calculate your annual payment on the loan for a 70% loan (30% down payment). Then compute the expected surplus or deficit PER ACRE Sale price per acre - Down payment (30%) = Loan amount (70%) x Amortization factor (30 years @ 7 %) = Annual payment Income Available (Adjusted net return page x 0.90) net return from previous (Income payment) Surplus/Deficit c. Does it cash flow? d. If not, how might you address the cash flow deficit? e. What is the maximum loan that will cash flow (per acre)? [Note: you are likely more familiar with asking what the annual (or monthly) payment will be for a particular loan amount. Here you are asking a different question: given the cash flow (maximum payment), how much can I borrow?] Expected Net Return per acre $ % tillable acres Expected Return per acre adiusted for tillable = $ acres (amortization factor) = S Maximum loan per acre that will cash flow f. Given your projected land value, how much down payment will you need per acre? (Your land value minus your maximum loan per acre from above) g. For a loan that will cash flow, how much is this down payment for the total 70 acres? 4. Construct the first 4 years of the amortization table for WHOLE PARCELL AND TOTAL LOAN using the table below. For the beginning principal balance, use 70 times the per acre "Loan amount (70%)" from your answer to 3b. (Answer 3b x 70 Acres = Total Loan) Principal Paid Year Beginning Balance Interest Paid Ending Balance ayment 1 2 3 4 a. How much of the loan principal have you paid down by the end of year 4? What percent of the original principal amount does this account for