1 LocationA 2 Initial investment 3 Residual value 4 Annual depreciation 3,000,000 0 300,000 6 Proiected income 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 7 Revenues 8 Expenses Net income 1,219,000 950,000 269,000 1,219,000 950,000 269,000 1,219,000 950,000 269,000 1,219,000 950,000 269,000 1,219,000 950,000 269,000 1,219,000 950,000 269,000 1,219,000 950,000 269,000 1,219,000 950,000 269,000 1,219,000 950,000 269,000 1,219,000 950,000 269,000 9 11 Location B 12 Initial investment 13 Residual value 14 Annual depreciation 15 3,000,000 400,000 260,000 2028 1,297,000 1,000,000 2019 2020 1,297,000 1,000,000 2021 1,297,000 1,000,000 2024 1,297,000 1,000,000 2025 1,297,000 1,000,000 2026 1,297,000 1,000,000 2027 1,297,000 1,000,000 d income 17 Revenues Expen 19 Net income 1,297,000 1,000,000 297,000 1,297,000 1,000,000 297,000 1,297,000 1,000,000 297,000 297,000 297,000 297,000 297,000 297,000 297,000 297,000 21 Location C 22 Inltlal Investment 23 Residual value 24 Annual depreciation 25 26 Projected income: 27 Revenues 28 Expenses 29 Net Income 4,500,000 0 450,000 2019 1,548,000 1,140,000 2020 1,548,000 1,140,000 2021 1,548,000 1,140,000 2025 1,548,000 1,140,000 2026 1,548,000 1,140,000 2027 1,548,000 1,140,000 2028 1,548,000 1,140,000 2022 2023 1,548,000 1,140,000 1,548,000 1,140,000 408,000 1,140,000 408,000 408,000 408,000 408,000 408,000 408,000 408,000 30 31 Location D 32 Initial investment 33 Residual value 34 Annual depreciation 35 36 Projected income: 37 Revenues 38 Expenses 39 Net income 40 41 Location E 42 Initial Investment 43 Residual value 44 Annual depreclation 45 46 Pro 47 Revenues 2,000,000 320,000 210,000 2019 2020 2021 2022 2023 2024 2025 2026 841,600 740,000 101,600 841,600 740,000 101,600 841,600 740,000 101,600 841,600 740,000 101,600 841,600 740,000 101,600 841,600 740,000 101,600 841,600 740,000 101,600 841,600 740,000 101,600 2,000,000 250,000 income 2019 2020 2021 2022 2023 2024 2025 2026 853,800 700,000 153,800 858,800 700,000 158,800 863,800 700,000 163,800 868,800 700,000 168,800 868,800 700,000 168,800 868,800 700,000 168,800 868,800 700,000 168,800 868,800 700,000 168,800 Expenses* 49 Net income 50 51Expense figures shown include depreciation. With the exception of depreciation, all expenses are assumed paid in cash. 1 Present values 2 The PV() function is what you use to calculate present values for both single amounts and 3 annuities, or even situations where you have both (a recurring cash inflow and a residual 4 value, for instance). To calculate the present value of a single amount: 7 6 Future value Discount rate Number of periods 1,000,000 8.00% 10 10 Present value (463,193) 12 13 Note the negative answer. By convention, cash inflows are shown as positives and cash 14 outflows are shown as negatives. Change some of the parameters (D5, D6, and D7) and note 15 how the answer change:s 16 17To calculate the present value of an annuity: 18 19 20 21 Annuity payment Discount rate Number of periods 200,000 8.00% 10 Present value (1,342,016) 24 Future values 25 You would use the -FV() function to translate known present values and or annuity payments 26 into a future value 27 28 29 30 31 32 Present value Discount rate Number of periods 1,000,000 8.00% 10 Future value (2,158,925) Annuity payment Discount rate Number of periods 200,000 8.00% 10 34 35 36 37 38 Future value (2,897,312) 38 39 Unknown discount rates 40 You can use the =RATE() function when you know the present and future values, but don't 41 know what the discount rate is. Make sure you put in cash inflows and outflows with 42 opposite signs. 43 Present value Future value Number of periods (1,000,000) 2,000,000 10 45 46 47 48 49 50 51 52 53 54 Discount rate 7.18% Annuity payments Future value Number of periods (150,000) 2,000,000 10 Discount rate 6.24% 56 The IRR() function is also very helpful. You can enter a range of values representing an 57 initial investment (as a negative number) followed by annual cash inflows and get an 58 internal rate of return value. 59 60 61 62 63 64 65 Initial investment Year 1 Year 2 Year 3 Year 4 (5,000,000) 1,800,000 1,800,000 1,700,000 1,600,000 Internal rate of return 14.58% 67