show formulas in excel

5. Assume you have won the lottery and have the choice of (1) receiving $1,000,000 now or (2) receiving $120,000 each year for 20 years. Opportunity cost of capital is 10 percent, what is the future value of the first option? Opportunity cost of capital is 10 percent; what is the future value of the second option? Which is the better option? Opportunity cost of capital is 8 percent; what is the future value of the first option? Opportunity cost of capital is 8 percent, what is the future value of the second option? Which is the better option? 6. Given the baseline gross receipts (no water restriction scenario) of irrigated cottom production versus the conversion of irrigated acres to dryland scenario, calculate the net present value (NPV) using a discount rate of 2.25% over the 10-year time period. What is the NPV of the baseline scenario? What is the NPV of the convert to dryland scenario? What is the loss in NPV due to conversion to dryland? Year Baseline Convert to Dryland Cash Flow ($) Cash Flow ($) 43,841,598 42,745,984 2 41,473,691 40,343,665 3 39,225,605 38,074,361 4 37,095,304 35,931,035 5 35,141,529 34,007 707 6 33 326,061 32,229,290 31,604,896 30,543,673 8 29,973 180 28,946,113 28,426 293 27 432,091 10 26,959,839 25,997,313 7. Assume a farmer has a choice of purchasing either a subsurface drip irrigation (SDI) system or a low energy precision application (LEPA) center pivot. The SDI system would cost $1200 per acre to install versus $556 per acre for the LEPA system. The estimated life for each system is 20 years. The salvage value at the end of the useful life is 20% of the initial investment. Assume that the discount rate of money is 2.25% Calculate the present value of the salvage value for SDI per acre. Sum the system investment with the present value of the salvage to get the total net investment for SDI per acre. Calculate the present value of the salvage value for LEPA per acre. Sum the system investment with the present value of the salvage to get the total net investment for LEPA per acre. Conclude which system would have a higher net investment. What are some other things to consider for the investment? Terms, Definitions, Abbreviations, and Formulas: Present Value (PV) - The number of dollars available or invested at the current time, or the current value of some amount to be received in the future. Ending lump sum FV PV = (1 +1)" Annuity (Payments) 1-(1+1)-" PV = PMTX Future Value (FV) - The amount of money to be received at some future time, or the amount a present value will become at some future date when invested at a given interest rate. Beginning lump sum FV = PV(1 + i)" Annuity (Payments) FV = PMT (1+1)" - 1 Payment (PMT) - The number of dollars to be paid or received at the end of each of a number of time periods. Annuity - A term used to describe a series of equal periodic payments (PMT). Payments may be either receipts or expenditures. Interest Rate (i/rate) - Also called the discount rate in some applications. It is the interest rate used to find present and future values, often equal to the opportunity cost of capital. Discount Rate (i/rate) - The opportunity cost of capital representing the minimum rate of return required to justify the investment. Time Periods (nper) - The number of time periods to be used for computing present and future values Net Present Value (NPV) - The sum of the present values for each year's cash revenue (P./value) minus the initial cost of the investment (C), if applicable. u P P2 Pr NPV = *(1 + 1)1*(1 + )2*** (1 + i)n