please show formulas to use in excel for the calculations
your workbook named "Investment Analysis". Please include an input area and an answer area for each problem. You will have an answer for each bullet point. Formatting is up to you. Just make sure it is easy to tell where your answers are. 1. Assume you purchased land for $150,000. Excluding any profit, what would you have to sell the land for to earn a 7 percent annual return on your investment after 5 years? After 8 years? 2. If a 6.5 percent annual return can be expected: How much would a 20-year old person have to put into a retirement fund each month to have $1,000,000 in the fund upon retirement at age 65? What would the contribution be if they had started investing at age 25 At age 35? 3. You know that you could afford to make a monthly payment of $450 towards a short- term loan of 5 years, but are wanting to know how much in total you could borrow with that level of payment. How much could you borrow with an annual interest rate of 8 percent? Of 6.5 percent? 4. A producer gets a loan to purchase a new piece of equipment for $250,000. What is the annual principal and interest payment for the loan with annual payments over 20 years at a 6 percent interest rate? How much (in total) would the producer pay in interest charges over the life of the loan? What is the annual principal and interest payment for the loan with annual payments over 15 years at a 6 percent interest rate? How much in total) would the producer pay in interest charges over the life of the loan? If the producer paid the equipment off sooner (15 years), how much interest would be saved? 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 cotton production versus the conversion of irrigated acres to dryland scenario, calculate the 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 (5) Cash Flow ($) QUAN 43,841,598 41,473,691 39.225.605 37,095,304 35,141,529 33,326,061 31,604,896 29,973 180 28.426,293 26,959,839 42,745,984 40,343,665 38,074,361 35,931,035 34,007,707 32,229,290 30,543,673 28,946,113 27,432,091 25,997313 7 8 9 10 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 PV = (1 + i)* FV Annuity (Payments) PV = PMT x 1-(1+1)-* 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 X (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. NPV = 0 + 0 + atoa+n+46- Using Excel to make Financial Calculations: You should be used to working these problems by hand, with a financial calculator, or using an app. The advantage of using Excel to make financial calculations is that it is easier than working problems by hand and is also a way to figure out financial problems without having to have a special calculator or app. We will use the following functions to make calculations in Excel: PV, FV, PMT, and NPV. For each of the following problems, follow these steps to find the answers: 1. Read the problem. 2. Decide what factor you are solving for: PV,FV, PMT, or NPV. 3. Define each input variable (rate, nper, pmt, PV,FV, etc.) in the problem. 4. Make sure that your rate matches the time period you are solving for (for example, when solving for monthly payments, you should have a monthly interest rate as the input rather than an annual rate) 5. Set up an area to solve the problem in Excel. Your area should include: a. The problem number. b. An input area. The input variable values and the abbreviation of each (similar to the in-class example) C. An answer area. The answer(s) being solved for as well as the abbreviation and time period (if applicable) for each 6. NOTE: In Excel, if a person is paying money out, it should be entered as a NEGATIVE number. On the other hand, if a person is receiving money, it should be entered as a POSITIVE number. **This must be entered correctly, or your answer will be incorrect.** Think through this step. Moneycoming in! Or out 7. Solve for your answer. To start your formula, make sure to use the PV, FV, PMT, or NPV function, depending on what you are solving for a. If using the 'insert function' wizard , the input variables in bold are required and not bold are not required. However, if you do not have one of the bold items to enter, you may need to enter a non-bold item for the formula to work. For any item you do not have, you can simply skip it. b. If looking at the formula in the regular edit cell view, the function "helper" (below the cell when active) will indicate variables that are not required with brackets (). For any variable you do not have, simply place another comma there to skip it. 8 Answer 9 = V(SAS4,ASSAS inners and type leave blanke Skips pmt
