what is it thats missing

i will resubmit later

. B D E G H Enter your name in this box Tutorial 3 - Present Value of Uneven Cash Flows Find the present value of an uneven cash flow stream invested for various compounding periods and at different discount rates The Problem RST Corporation has the opportunity to purchase a CNC machine that promises cash flows of $500 in year year 2, and $100 in year 3. The interest rate is 7% What is the present value of the cash flows from this mac Uneven cash flow A series of cash flows in which the amount varies from one period to the next Hint. Unless told otherwise, assume that cash flows occur at the end of the period! Finance Concept: PV = CF/(1+r) + CF/(1+r) + CF,/(1+r) Present Value Sum of the present values of the individual cash flows PV = 500/(1+0.07) + 300/(1+0.07% + 100/(1+0.07) PV - 467.29 +262.03 + 81.63 = $810.95 In Words: The value today of future cash flows discounted at seven percent is $810.95 Spreadsheet Solution: Use formulas to find your answer by completing this PV Table: Today Year 1 7% 1 500 a Interest Rate b Time c Cash Flow d PV interest factor e Present Value 0 Year 2 7% 2 300 Year 3 79 3 100 1 First, we must calculate the PV Interest Factor for each cash flow in row d. PVIF = 1/(1+r) In cell F33 enter the formula 1/(1+F30)^1 and copy to cells G33 and 33 read me Tutorial 3 + Type here to search O et TVM 2 Then we find the PV of each cash flow in now e. In cell F34 enter the PV formula: CFPVIF = F32 F33 and copy to ce 3 Lastly, we sum the PVs to get the PV of the annuity in cell E34. In cell E34 enter the formula: sum(F34:13 4 Your answer should be: The PV of the uneven cash flows is $810.95 Note. You can change any of the inputs and Excel will automatically recompute Test your skills What is the Present Value of the cash flow above if the discount rate is 1%7 Enter 01 for each interest rate and copy and paste your answer here. Answer $886.20 Spreadsheet Solution with the Excel Wizard: Year 1 Today 0.07 Year 2 Yeat 3 0 1 a Interest Rate bTime d Cash Flow dPresent Value 500 3 100 300 1. Put the cursor on E65 and click the function wizard (2) click financial se down to NPV, and click OK. J is located on your standard menu bar NPV is the Net Present value because some of the cash flows may be negative NRV is also for cash flows that are not constant read me Tutorial 3 + Type here to search O COM TV Pigment Numbe A B CL D E G H K Note: You can also enter each cash flow in a separate value box by pointing and clicking sing the NPV formula directly: Use the built-in Excel formula: NPV(rate, CFS) Inputs rate 0.07 cash flows = 500 300 100 CF CF CF Tutorial 3 - Present Value of Uneven Cash Flows 7 8 9 Find the present value of an uneven cash flow stream invested for various compounding periods and at different discount rates. The Problem RST Corporation has the opportunity to purchase a CNC machine that promises cash flows of $500 in year 1, $300 in year 2, and $100 in year 3. The interest rate is 7% What is the present value of the cash flows from this machine? Uneven cash flow. A series of cash flows in which the amount varies from one period to the next 10 11 12 13 14 15 Hint Unless told otherwise, assume that cash flows occur at the end of the period! 16 17 Finance Concept: PV - CF/(1+r) +CFy/(1+r) + CF/(1+r) 19 Present Value Sum of the present values of the individual cash flows 20 PV = 500/(1+0.07) + 300/(1+0.07) + 100+0.07) 21 PV =467.29 + 262.03 + 81.63 = $810.95 22 23 I Words: The value today of future cash flows discounted at seven percent is $310.95 24 25 Spreadsheet Solution: 26 27 Use formulas to find your answer by completing this PV Table: 28 29 Today Year 1 Year 2 Year 3 30 a Interest Rate 7% 7% 31 b Time 0 1 2 3 32 Cash Flow 500) 100 33 PV interest factor 34 e Present Value 35 36 1 First, we must calculate the PV Interest Factor for each cash flow in row d. PVIF = 1/(1r) 37 In cel F33 enter the formula:=1/(1+F30)^1 and copy to cells G33 and 33 read me Tutorial 3 O Ready 11 Type here to search O 1 TVM TVM G B C D EF G H Welcome to the TIME VALUE of MONEY TUTORIAL What you need to know: 1. The next worksheet has a review of finance and Excel basics 2. The problem worksheet is the tutorial sheet you must solve, print, and hand in 3. Credit is given only for the printed problem - with your name printed in top box Fill out the cells with boxes around them like this => 4. The Problems. Answers are usually provided for you to check your work Some questions and problems require you to follow a previous procedure. ATVM tutor is available to assist you in the PC lab Check for hours. read me first Tutorial1 problem_1_review Type here to search o c ne insert D VIEW Be careful files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Pa B C D E F Tutorial 2) Present Values What you will learn: 1. How to find the present value of an annuity 2. How to write formulas with relative and absolute references 3. How to copy formulas 4. How to use a table 5. How to set up data for a chart Fol out the cells with boxes around them like this B C D E F G H K Enter your name in this box => 4 5 6 Tutorial 3 - Present Value of Uneven Cash Flows 7 8 9 Find the present value of an uneven cash flow stream invested for various compounding periods and at different discount rates The Problem: RST Corporation has the opportunity to purchase a CNC machine that promises cash flows of $500 in year 1, $300 in year 2, and S100 in year 3. The interest rate is 7%. What is the present value of the cash flows from this machine? 10 11 12 13 14 15 Uneven cash low A series of cash flows in which the amount varies from one period to the next Hint Unless told otherwise, assume that cash flows occur at the end of the period! 16 17 Finance Concept: PV=CF/(1+r) + CFy/(1+r)*+CF/(1+r) 19 Present Value Sum of the present values of the individual cash flows 20 PV = 500/(1+0.01) + 300/(1+0.079 + 100/(1+0.07) 21 PV =467.29 + 262,03 + 81 63 = $810.95 22 23 IN Words: The value today of future cash flows discounted at seven percent is $810.95 24 25 Spreadsheet Solution: 28 27 Use formulas to find your answer by completing this PV Table: 28 29 Today Year 1 Year 2 Year 3 3D a Interest Rate 7% 7% 7% 31 b Time 0 1 3 32 Cash Flow SOO 100 33 PV interest factor 34 e Present Value 35 36 1 First, we must calculate the PV Interest Factor for each cash flow in row d PVIF = 1/(1+r) 37 In cell F33 enter the formula = 1/(1-F30)^1 and copy to cells 633 and 133 read me Tutorial 3 00 E Type here to search o C Alignment X Vf C D E F G Present Value = Sum of the present values of the individual cash flows PV = 500/(1+0.07) + 300/(1+0.07 +100/(1+0.07) PV = 467 29 +262.03 + 81.63 $810 95 Words: The value today of future cash flows discounted at seven percent is $810.95 neet Solution: Use formulas to find your answer by completing this PV Table Today Year 1 7% Year 2 7% 2 0 Interest Rate b Time c Cash Flow dPV interest factor e Present Value Year 3 7% 3 100 1 500 1 First, we must calculate the PV Interest Factor for each cash flow in rowd PVIE = 1/(1+r)! In cell F33 enter the formula 1/(1+F30)^1 and copy to cells G33 and 133 You must change the exponents on cells G33 and H33 2. Then we find the PV of each cash flow in rowe. In cell F34 enter the PV formula: CFPVIE +F32 F33 and copy to cells G34 and H 3 Lasty, we sam the PVs to get the PV of the annuity in cell E34 In cell E34 enter the formula: sum(F34:34) 4 Your answer should be: The PV of the uneven cash flows is $810.95 Note: You can change any of the inputs and Excel will automatically recompute I skills What is the Present Value of the cash flow above if the discount rate is 1967 Enter 01 for each interest rate and copy and paste your answer here. read me Home New Roman La Copy 10 AA SEE 23 Wrap Text BIU Paste Format Painter Clipboard IM IM Merge & Center $ %) Font Alignment V124 Number T X fox . B D E F G H 46 4 Your answer should be The PV of the even cash flows is $810,95 47 48 Note You can change any of the puts and Excel will automatically recompute. 49 50 51 52 Test your skills 53 54 What is the Present Value of the cash flow above if the discount rate is 1957 55 Erter 01 for each interest rate and copy and paste your answer here. 56 Answer: $886.20 57 58 59 Spreadsheet Solution with the Excel Wizard: 60 81 Today Year! Year 2 Year 3 62 Interest Rate 0.02 63 b Time 0 1 2 64 Cash Flow 500 100 65 dPresent Value 66 67 300 1. Put the cursor en E65 and click the function wizard). cick financial scrol 68 down to NPV, and click OK J is located on your standard men bar. 69 70 NPVB the Net Piesai be because of the Boss may be negative 71 NPK for cash flows that are constant 2. The dialog box will appear Enter E62 or 07 for the Rate and the range of 72 cells containing the cash flows: F64 H64, for value I. Click OK 73 74 Note: You can also enter each cash low ba separate vahe box by pointing and clicking 75 76 77 Using the NPV formula directly 78 79 Use the built-in Excel formula:=NPV(rate, CFS) read me Tutorial 3 11 Type here to search O CE Number 124 B C D E FL G H K Note: You can also enter each cash flow in a separate value box by pointing and clicking Using the NPV formula directly: Use the built-in Excel formula: NPV(rate, CF) Inputs: rate 0.07 cash flows 500 300 100 CF CF CF in year 1 CF in year 2 CF in year 3 CF 1. In cell H81 type: =NPVE 2. Then click on cell F83 or enter 0.07 and enter a comma 3. Then put your cursor on cell F84 (at the first CF of $500) and drag the cursor to F86 and enter the ending parenthesis and then click enter. 4. Your answer should be $810.95 Hint: This is similar to your calculator cash flow keys PV 5 3 Using the Table and PV formulas: 7 B Time CF 9 0 00 1 31 2 32 3 03 04 rate 05 08 PV SUM 07 08 Data read me Tutorial 3 1. Type in your cash flows in cells D99 through D102. 2. Type in your discount rate in cell D101 3. In cel E99 type: =PV(SDS101.C99,0, D99) 4. Copy to cells E100 E102 5. In cell E 106 type: SUM(E99 E102) 6. You should get $810.95 as the PV SUM with the inputs from the previous problem. CF,0 + Type here to search ED File Home Insert Page Layout Formulas Data Review View Help Acrobat Times New Roman - 10 X Cut Copy Format Painter ' ' 23 Wrap Text Paste BIU A Merge & Center $ - % 48.5 Clipboard 5 Font Alignment 5 Number V124 D f 4 A B C DEFIGH 91 J K L 4. Your answer should be $810.99 92 93 Hint This is similar to your caleulator cash flow keys 94 95 96 Using the Table and PV formulas: 97 98 CF PV 1. Type in your cash flows in cells D99 through D102 99 0 2. Type in your discount rate in cell DIO! 100 1 3. In cell E99 type PV($D$101.699,0,-D99) 101 2 4. Copy to cells E100-E 102 102 3 5. In cel E106 type: =SUM(E99 6102) 103 6. You should get $810 95 as the PV SUM with the 104 rate inputs from the previous problem. 105 CF, 0 106 PV SUM 107 10B 109 Test your skills 110 What is the present value of a cash flow of $1,000 today, $2,000 at the end of the next year (time1). 111 $3,000 at the end of year 2, and $4,000 at the end of year 3, discounted at 5% (rate 05)? 112 113 114 Your answer should be 3908 1.20 115 PRINT your spreadsheets. 116 End of Spreadsheet2 117 118 119 120 121 122 123 124 125 128 127 129 read me Tutorial Type here to search O A Neveve View Help Acrobat IEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protec B C D E F G H Tutorial 5. Future Values of Uneven Cash Flow Stream What you will learn: 1. How to find the future value of uneven cash flow streams 2. How to use an Excel table 3. How to write formulas and enter values into an Excel table Fill out the cells with boxes around them like this => PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safe to stay in Protected View A B C D E F G . Enter your name in this box K Tutorial 6 - Caleulating Loan Payments and Loan Amortization ou will determine the loan payment and amortization schedule for a fixed rate loan (such as a car loan or a house mortgage) Problem 1: You are planning to purchase a car that costs $20.000. You can secure a loan from a credit union. The loan -Eficer informed you of the loan terms. The annual percentage rate (APR) is 6%, and the loan must be repaid in 5 years. Vhat will be your monthly payments to the credit union? PVIFA is used to calculate loan paytices Finance Concept: PV - PMT PVIFA = PMT [1- (1/(1++))} PMT PV (11-(1/(1+r)b) We'invert the formula for PV of an annuity, and solve for payment or cash flows Numerical Solution: PVPMTPVIFA. 20,000 = PMT [1-(1/(1+06.06 20,000 - PMT [1-74726.06 20,000 PMT. 42124 PMT=20.000/42124 PMT= $4,747.88 with calculator In Words: The yearly payments on a $20,000 loan for 5 years at 6% are $4,747.93 by the Wizard (The difference is due to rounding errors Excel Solution for Monthly Payments: Note Interest Rate is formatted in percent by clicking on menu button -20000 Loan Amount Annual Interest Rate Term of Loan in Years Monthly Interest Rate 5 Enter monthly Interest Rate: E35/12 TO . read me Tutorial 6 Type here to search O CE View Help Acrobat O PROTECTED VIEW Be Careful-files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View En C86 K L A B D E F G H 28 by the Wizard. The difference is due to rounding errors 29 30 Excel Solution for Monthly Payments: 31 32 Note Interest Rate is formatted in percent by clicking on menu button 33 34 Loan Amount -20000 35 Annual Interest Rate 698 38 Term of Loon in Year's 5 37 Monthly Interest Rate Enter monthly Interest Rate: = E35/12 38 Term of Loan in Months Enter per in months: E36 *12 39 40 Woticed the interest rate to agree with the cumber of preds 41 Solutions 42 43 44 45 Monthly Payment Annual Amount Paid Total Amount Paid Interest Paid Over Term of Loan In cell F42 enter: PMT(E37,E38 E34), or use Par Wizard In cell E43 enter: E42 * 12 In cell E44 enter E43 E36 In cell E45 enter: =E44E34 (a (+) sign be 34 is negative) 47 Monthly Payments are calculated by the Excel Wizard Function, or by using the formula for 48 PMT with the inputs (interest term. principal). Check rumbers: Total Amt paid $23,199 49 50 Test Your Skills: 51 What is the monthly payment if you buy a $30.000 car, holding time and rate constant? 52 answer is $$79.98 53 54 Problem 2 You are planning to purchase a car that costs $20,000. You want to shop around and compare different loan rates and terms before you make a decision. Now you'll want to use a Table to compete your total interest and principal 55 so they can be compared 58 57 1. Amount 20000 58 Ance Interest Rate see table Step 1 In cell Dot enter -PART(C6V12 SES60, SE$57) 59 Ten of Leon in Years 5 Watch $ signs. Copy by draping the cursor 60 Total number of months 60 Excel function: PMT (rate, term, principal) 61 Divide the interest rate by 12 to convert to monthly 62 With Various Interest Rates: 63 Rate PMT Total Interest Interest - Principal read me Tutorial 6 A Type here to search O Search AutoSave Om aus. TVM 06 loan - Protected View File Home Insert Page Layout Formulas Data Review View Help Acrobat PROTECTED VIEW Be careful from the internet ca cortan Vese. Unless you need to ed asset to stay in Protected View 86 X Enable Eoling 1 A B C D E F G H You are planning to purchase a car that costs $20.000. You want to shop around and compare different loan rates and terms before you make a decision Now you'll want to use a Tave to compute your total interest and principal so they can be compared L M Lean Art -20000 Amal Interest Rate see the Tenn of Loan in Years Totalbet of months 60 Step 1.Inced Dot enter PMT(C6V 12.SE$60.SE$57) Watch $ signs Copy by draging the corner Excel functia PT = (rate, ter principal Divide the sterest rate by 12 to convert to monthly "Torres Interest Principal 2 With Various Interest Rates Rate PM 2.096 3096 4025 5.00% 6.0% 7.034 8.046 PM @ 2%= 351 In Prin-21033 Step 2 Tocal Interest Coaster D660-SES57dcopy down Step 3 amet paid Interest Pics In Guter E64-SES$7 and copy down 2 s With Varones (Years): Merly B Year PMI Total derest Interest Principal 7 Tapets rate 0.12 loan prin 20000 temin y 12 1911 11 PMT - (rate, term, principal) 12 -C012 yrs 12-20000) Staplete: -PATISK878 12.SK 80-11 SKS79) and copy down Step 2 L 172 1734C77"SKSSO-SK579 B 9 Step 3. In 27 E27 SKS79 0 check bers. 12 yrs. pert-$361, Int+P$37826 #1 Step 4 Copy down cells in table read me Tutorial Type here to search o c File Home Insert Page Layout Formulas Data Review View Help Acrobat PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. C86 X & B A B C D E F G . K B2 11 PMT=(rate, term, principal) 93 12 = (06/12 yrs *12,-20000) 84 B5 Step 1. In cell D77 enter: PMT(SKS78/12,5KS80-12,5KS79) and copy down. 86 87 Step 2. In E77 enter =D77*C77"SKS80+SKS79 8B 89 Step 3. In G77 enter =E77-SK879 90 check numbers: 12 yrs pet-$263, Int+Prin$37,826 91 Step 4. Copy down cells in table 92 93 What is your choice of loan rates and terms? 94 95 98 Problem 3: 97 You are planning to purchase a car for $20.000. You decide to get the loan from the credit union. It is for 696 annually but you decide to pay it off in 3 years You want to see the loan amortization table for your loan to see how the 98 interest and principal are being paid off Fill in the inputs and the table will automatically calculate for you. 99 100 Loan Enter the loan as-20000 101 Monthly PMT You must calculate ParT =(rate/12, term" 12. principal) 102 Rate alate (006) 103 Term years of loan (3) 105 106 Loan Amortization Table Constant PEC: S0.00 Principal 107 0 108 Month 109 110 2 111 112 4 113 114 6 115 116 117 9 Beg Principal Balance 0 0 0 0 Total Monthly PMC 0 O 0 0 0 Monthly Loan Interest 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Principal Reduction 0 0 0 0 0 0 0 0 0 Ending Principal Balance 0 0 0 0 O 0 0 0 0 read me Tutorial 0 Type here to search o c C a K B C D Loan Monthly PMT Rate Term E F G H Enter the loan as-20000 You must calculate PMT = (rate/12, term*12, principal) > Sheet1 Tutoriale o B ICIDI G 00 SOUN- Enter your name in this box 3 4 5 Tutorial 9 Discounted Cash Flow Analysis Estimating depreciation of an asset, calculating cash flows, and estimating NPV. 8 Problem 1: Diltz Farms is considering investing in an automated egg-sorting system to increase production for international (web. based) sales of Ditz Farms' products. The new system will cost $3,000 including installation. It will be fully depreciated in 5 yrs (straight-line) to zero and generate $150 after-tax gain at the end of the projected period (year 6). The initial working captital will be $300 and will be $500 in year one and increase each year thereafter by 5 percent. Revenues generated from the egg sorter are expected to be $900 in year one, and increase by five percent each year. Expenses are ten percent of revenues. Daitz Farms opportunity cost of capital is 8.5% Using the discounted cash flow analysis, should Diltr Farms invest in the machinery? 9 What is the NPV of the ea-sorter project? 10 11 France Concept: When making capital investment decisions we must consider the effect of a change 12 in cash flows. The Excel spreadsheet is extremely useful in calculating cash flows allowing us to 13 compare different assets or depreciation methods (what-analysis) 14 15 Step 1: Find the accounting yearly depreciation with the straight line method: 16 Depreciable Basis Cost of the Asset Salvage Value 18 SL (Straight-Line Depreciation (Initial Cost-Selvaze Value number of years 19 Hint Salvage Value for depreciation in cell 2 20 Cost of Asset 1. Enter values from the problem in cells E20, E21, and E22 21 Life of Asset in Years 2. In cell E23 enter-E20-E22 22 Salvage Value 3. In cel E24 enter. -E23/E21 [yearly depreciation) 23 Depreciable Basis 4. Yearly depreciation should be $3.0003 - 5100 24 Yearly depreciation 3. Salvage Value for year 6 - +150 [from problems 25 Step 2: Creating a table to calculate the After Tax Cash Flows and NPV (in millions): 2b 27 YEAR: 0 1 3 4 28 Initial Investment 6 29 Salvage Value 30 Working capital 31 Change in Wk Cap 32 Revenues 33 Expenses 34 Depreciation 35 Pretax pro 36 Tax 3595 37 Profiter ta 38 CF Bom operations 39 Cash Flow: 40 CF from capital investments read me Tutorial 9 5 + Type here to search O - Protected View le Home Insert Page Layout Formulas Data Review View J PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Help Acrobat 3 In year 1[cell F30) enter the new wkg cap (500), and calculate the change in wkg . B D E F G Profit after tax CF from operations Cash Flow: CF from capital investments CF from working capital CF from operations Total cash flows Discount factor PV of cash flow Net present value Discount rate Completing the spreadsheet is a simple use of Excel mathematical functions. You might want to print the instructions first Cash Flow Front Operations In year zero enter the initial investment, the initial capital and the changin wir cap In year [cel F30) enter the new wg cap [800, and calculate the change in wkg cap by subtracting year o from year I In cell F31 enterF30-F30 3. In year two, enter year one's whycup 15-F90103. Copy the formula through year. Also copy chg in wkg cap fomula to years 2.6 4. In year one enter reveries. In year two enter year one "LOS-F32*105] and copy to years 5. In year ore enter expenses from problemin year two multiply year one by 11 and copy across 6. Enter depreciation in year one from the depreciation table and copy remember ) 7. Yearly pretat profit is revenues minus expenses and depreciation [F35-F32 F33-F34 Copy across 8. Calculate the tax by multiplying pretut profit by the tax rate. (35) F3-F350 35] copy across 9. Subtract the tax from the pretax profit to get the after tat profit. [F3-F35-F36copy across 10 To obtain CF from operations in year initial investment chargcap (walbe a negative number] 11. For years 1-5 add Profile After Tax to Depreciation (F3H+F34). Copy across. Year 6 is blank Caleting the NPT 1 Cash Flow from capital investments.ester-E23 in cell 40 2. Cash Flow from working capital enter-Elin cell Eland copy across to J41 3. Cash Flow from operations in cell F12 ester-F38 and copy cross to J12. In cell 41 enter-31+K 29. 4 Total cash flows in cell El enster -SUMEDE) and copy across to K43 3. Discount rate: enter the required retum (08:) in E48 6 Discount factor in cell 4t enter (SES) and copy across Change the esponent in yrs 16 7. PV of cash flow in cel E45 enter -E46E43 and copy across 8. Net present value in cel El enter -SUME 45 K45) Some checkers Year 6 change in working capital -605 Change from wkg cap (K41) - 738 Total cash flows for years 30-01 With a required retum of $59, sheuld DiteFarms go ahead with the newes sorter? What is the NPV of these sorter project What is the IRR of the project? read me Tutorial 9 CS3 fo In year 1[cell F30) enter the new wkg cap (500), and calculate the change in w A B L D 1. Cash Flow from capital investments: enter-E28 in cell E40 2. Cash Flow from working capital enter Elin cell Efland copy across to J41. 3. Cash Flow from operations in cel F12 enter-F38 and copy across to 142. In cell K41 enter-K31-29 4. Total cash flows: in cell E13 enter -SUME 60E42) and copy across to Ko 5. Discount rate: enter the required retum (083) in EAS 6 Discount factor in cel El enter:-1+SESAS) and copy across Change the exponent in yrs 16. 7. PV of cash flow.incell E45 enter E44E43 and copy across 8. Net present value in cell E46 enter -SUME 45X45) 4 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 Some check umbers: Year 6 change in working capital - 608 Change from akg cap (41) - 758 Total cash flows for year (34)-821 With a required return of 85%, should Ditz Fams zo ahead with the new egg-sorter? What is the NPV of the egg sorter project? What is the IRR of the project? What is the NPV if the required retam is 994? Problems 2: Your fim recently purchased an industrial machine costing 505.000. It is classified as a seven-year property under MACRS 87 What are the annual depreciation allowances and end of the year book values for this machine? 88 89 Finance Concept: Forta purposes the depreciation expense is computed under MACRS, which was 90 enacted as part of the Tax Refon Act of 1956. The depreciation is larger at the beginning Bepinning Book 7 Year Depreciation Ending Book 92 Year Value MACRS Allowance Value 93 1 14 1996 2 24.4945 95 3 17.996 96 12.4996 97 5 8.9396 98 8.9196 99 2 89396 100 8 101 102 103 Solo 104 1 Enter the Beginning Bok Value cost of the machine) in cell 09. 105 2 Calculate the remaining Beginning Back Values: enter -193 in cell E91 and copy down the column 106 3 Calculate the Depreciation Allowance by multiplying SES91 (Beg BV) 793 (ARS) 107 4 Find the Ending Book Value by subtracting the depreciation from the Beginning Book Value 108 Call your for den the.com read me Tutorial 9 E Type here to search O PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View 3 X In year 1[cell F30) enter the new wkg cap (500), and calculate the change in wke E F G H A B C D Beginning Book Value Depreciation Allowance Ending Book Value Year 1 2 3 4 3 6 7 8 -7 Year MACRS 14.29% 24.49% 174996 12.4996 8.9395 89396 8.9396 4 15 16 07 08 09 10 Solution: 1 Enter the Beginning Book Value (cost of the machine) in cell E93 2 Calculate the remaining Beginning Book Values: enter 193 in cell E91 and copy down the column 3 Calculate the Depreciation Allowance by multiplying SE$93 (Beg Bk V) F93 (MACRS %). 4 Find the Ending Book Value by subtracting the depreciation from the Beginning Book Value 3 Copy all your fomulas down the columns 6 Year 8 Ending Book Value should equalzere, and beg bk val - depreciation in year 12 Probleme 3: Petsy's Peaches has developed a new product, the Braseless Peach, which always stays peachy fresh Pesey's paid $85,000 to a marketing firm to survey the bruseless peach market. The potential sales were estimated at $250,000 per year. New equipment will be necessary to carefully handle the peaches. It cost $200,000 and will have fisted costs of $70,000 per year, and Variable costs will be 23% of sales. The new anti-bruise machine will be depreciated straight line for the four years of it's life and is the caly initial cost for the new "Peggy's Peaches, the Un Braised Ones". Peggy's pays 3495 tax and has a required retum 113 of 8% Calculate the NPV and IRR 114 115 Solution: 116 Entenbers and formulas to solve this problem. Use the Excel NPV and IRR functions to solve NPV and IRR 117 Step L. Find the Net Income for years 14 118 Depreciation - Initial cost - Salvage Value years 119 Assume Salvage Value - 0. 120 Net Income Year 1-4 121 Sales 122 from problem Variable Costs 25% of Sales 123 Fued Costs 124 free problem Depreciation 125 from HIS EBIT 126 EBIT -Sales-costs-dep Tates 127 Tares EBIT tax rate Net Income 128 NI-EBIT-tes 129 130 Operating Cash Flows EBIT 131 132 Depreciation Taxes read me Tutorial 9 + 1 Type here to search O Home Insert Page Layout Formulas Data Review Review View Help Acrobat ECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected V X for B D E F G Tutorial 10. NPV & IRR Investment Criteria What you will learn: 1. How to find the Internal Rate of Return 2. How to compare a project's IRR with it's NPV 3. How to write formulas and enter values into an Excel table Fill out the cells with boxes around them like this read mo Home Insert Page Layout Formulas Data Review View Help Acrobat COTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Pro XV fo B C D E F G HT Tutorial 11. Capital Structure What you will learn: 1. How to estimate the effect of financial leverage 2. How to write formulas in a complex table Fill out the cells with boxes around them like this => * . B D E G H Enter your name in this box Tutorial 3 - Present Value of Uneven Cash Flows Find the present value of an uneven cash flow stream invested for various compounding periods and at different discount rates The Problem RST Corporation has the opportunity to purchase a CNC machine that promises cash flows of $500 in year year 2, and $100 in year 3. The interest rate is 7% What is the present value of the cash flows from this mac Uneven cash flow A series of cash flows in which the amount varies from one period to the next Hint. Unless told otherwise, assume that cash flows occur at the end of the period! Finance Concept: PV = CF/(1+r) + CF/(1+r) + CF,/(1+r) Present Value Sum of the present values of the individual cash flows PV = 500/(1+0.07) + 300/(1+0.07% + 100/(1+0.07) PV - 467.29 +262.03 + 81.63 = $810.95 In Words: The value today of future cash flows discounted at seven percent is $810.95 Spreadsheet Solution: Use formulas to find your answer by completing this PV Table: Today Year 1 7% 1 500 a Interest Rate b Time c Cash Flow d PV interest factor e Present Value 0 Year 2 7% 2 300 Year 3 79 3 100 1 First, we must calculate the PV Interest Factor for each cash flow in row d. PVIF = 1/(1+r) In cell F33 enter the formula 1/(1+F30)^1 and copy to cells G33 and 33 read me Tutorial 3 + Type here to search O et TVM 2 Then we find the PV of each cash flow in now e. In cell F34 enter the PV formula: CFPVIF = F32 F33 and copy to ce 3 Lastly, we sum the PVs to get the PV of the annuity in cell E34. In cell E34 enter the formula: sum(F34:13 4 Your answer should be: The PV of the uneven cash flows is $810.95 Note. You can change any of the inputs and Excel will automatically recompute Test your skills What is the Present Value of the cash flow above if the discount rate is 1%7 Enter 01 for each interest rate and copy and paste your answer here. Answer $886.20 Spreadsheet Solution with the Excel Wizard: Year 1 Today 0.07 Year 2 Yeat 3 0 1 a Interest Rate bTime d Cash Flow dPresent Value 500 3 100 300 1. Put the cursor on E65 and click the function wizard (2) click financial se down to NPV, and click OK. J is located on your standard menu bar NPV is the Net Present value because some of the cash flows may be negative NRV is also for cash flows that are not constant read me Tutorial 3 + Type here to search O COM TV Pigment Numbe A B CL D E G H K Note: You can also enter each cash flow in a separate value box by pointing and clicking sing the NPV formula directly: Use the built-in Excel formula: NPV(rate, CFS) Inputs rate 0.07 cash flows = 500 300 100 CF CF CF Tutorial 3 - Present Value of Uneven Cash Flows 7 8 9 Find the present value of an uneven cash flow stream invested for various compounding periods and at different discount rates. The Problem RST Corporation has the opportunity to purchase a CNC machine that promises cash flows of $500 in year 1, $300 in year 2, and $100 in year 3. The interest rate is 7% What is the present value of the cash flows from this machine? Uneven cash flow. A series of cash flows in which the amount varies from one period to the next 10 11 12 13 14 15 Hint Unless told otherwise, assume that cash flows occur at the end of the period! 16 17 Finance Concept: PV - CF/(1+r) +CFy/(1+r) + CF/(1+r) 19 Present Value Sum of the present values of the individual cash flows 20 PV = 500/(1+0.07) + 300/(1+0.07) + 100+0.07) 21 PV =467.29 + 262.03 + 81.63 = $810.95 22 23 I Words: The value today of future cash flows discounted at seven percent is $310.95 24 25 Spreadsheet Solution: 26 27 Use formulas to find your answer by completing this PV Table: 28 29 Today Year 1 Year 2 Year 3 30 a Interest Rate 7% 7% 31 b Time 0 1 2 3 32 Cash Flow 500) 100 33 PV interest factor 34 e Present Value 35 36 1 First, we must calculate the PV Interest Factor for each cash flow in row d. PVIF = 1/(1r) 37 In cel F33 enter the formula:=1/(1+F30)^1 and copy to cells G33 and 33 read me Tutorial 3 O Ready 11 Type here to search O 1 TVM TVM G B C D EF G H Welcome to the TIME VALUE of MONEY TUTORIAL What you need to know: 1. The next worksheet has a review of finance and Excel basics 2. The problem worksheet is the tutorial sheet you must solve, print, and hand in 3. Credit is given only for the printed problem - with your name printed in top box Fill out the cells with boxes around them like this => 4. The Problems. Answers are usually provided for you to check your work Some questions and problems require you to follow a previous procedure. ATVM tutor is available to assist you in the PC lab Check for hours. read me first Tutorial1 problem_1_review Type here to search o c ne insert D VIEW Be careful files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Pa B C D E F Tutorial 2) Present Values What you will learn: 1. How to find the present value of an annuity 2. How to write formulas with relative and absolute references 3. How to copy formulas 4. How to use a table 5. How to set up data for a chart Fol out the cells with boxes around them like this B C D E F G H K Enter your name in this box => 4 5 6 Tutorial 3 - Present Value of Uneven Cash Flows 7 8 9 Find the present value of an uneven cash flow stream invested for various compounding periods and at different discount rates The Problem: RST Corporation has the opportunity to purchase a CNC machine that promises cash flows of $500 in year 1, $300 in year 2, and S100 in year 3. The interest rate is 7%. What is the present value of the cash flows from this machine? 10 11 12 13 14 15 Uneven cash low A series of cash flows in which the amount varies from one period to the next Hint Unless told otherwise, assume that cash flows occur at the end of the period! 16 17 Finance Concept: PV=CF/(1+r) + CFy/(1+r)*+CF/(1+r) 19 Present Value Sum of the present values of the individual cash flows 20 PV = 500/(1+0.01) + 300/(1+0.079 + 100/(1+0.07) 21 PV =467.29 + 262,03 + 81 63 = $810.95 22 23 IN Words: The value today of future cash flows discounted at seven percent is $810.95 24 25 Spreadsheet Solution: 28 27 Use formulas to find your answer by completing this PV Table: 28 29 Today Year 1 Year 2 Year 3 3D a Interest Rate 7% 7% 7% 31 b Time 0 1 3 32 Cash Flow SOO 100 33 PV interest factor 34 e Present Value 35 36 1 First, we must calculate the PV Interest Factor for each cash flow in row d PVIF = 1/(1+r) 37 In cell F33 enter the formula = 1/(1-F30)^1 and copy to cells 633 and 133 read me Tutorial 3 00 E Type here to search o C Alignment X Vf C D E F G Present Value = Sum of the present values of the individual cash flows PV = 500/(1+0.07) + 300/(1+0.07 +100/(1+0.07) PV = 467 29 +262.03 + 81.63 $810 95 Words: The value today of future cash flows discounted at seven percent is $810.95 neet Solution: Use formulas to find your answer by completing this PV Table Today Year 1 7% Year 2 7% 2 0 Interest Rate b Time c Cash Flow dPV interest factor e Present Value Year 3 7% 3 100 1 500 1 First, we must calculate the PV Interest Factor for each cash flow in rowd PVIE = 1/(1+r)! In cell F33 enter the formula 1/(1+F30)^1 and copy to cells G33 and 133 You must change the exponents on cells G33 and H33 2. Then we find the PV of each cash flow in rowe. In cell F34 enter the PV formula: CFPVIE +F32 F33 and copy to cells G34 and H 3 Lasty, we sam the PVs to get the PV of the annuity in cell E34 In cell E34 enter the formula: sum(F34:34) 4 Your answer should be: The PV of the uneven cash flows is $810.95 Note: You can change any of the inputs and Excel will automatically recompute I skills What is the Present Value of the cash flow above if the discount rate is 1967 Enter 01 for each interest rate and copy and paste your answer here. read me Home New Roman La Copy 10 AA SEE 23 Wrap Text BIU Paste Format Painter Clipboard IM IM Merge & Center $ %) Font Alignment V124 Number T X fox . B D E F G H 46 4 Your answer should be The PV of the even cash flows is $810,95 47 48 Note You can change any of the puts and Excel will automatically recompute. 49 50 51 52 Test your skills 53 54 What is the Present Value of the cash flow above if the discount rate is 1957 55 Erter 01 for each interest rate and copy and paste your answer here. 56 Answer: $886.20 57 58 59 Spreadsheet Solution with the Excel Wizard: 60 81 Today Year! Year 2 Year 3 62 Interest Rate 0.02 63 b Time 0 1 2 64 Cash Flow 500 100 65 dPresent Value 66 67 300 1. Put the cursor en E65 and click the function wizard). cick financial scrol 68 down to NPV, and click OK J is located on your standard men bar. 69 70 NPVB the Net Piesai be because of the Boss may be negative 71 NPK for cash flows that are constant 2. The dialog box will appear Enter E62 or 07 for the Rate and the range of 72 cells containing the cash flows: F64 H64, for value I. Click OK 73 74 Note: You can also enter each cash low ba separate vahe box by pointing and clicking 75 76 77 Using the NPV formula directly 78 79 Use the built-in Excel formula:=NPV(rate, CFS) read me Tutorial 3 11 Type here to search O CE Number 124 B C D E FL G H K Note: You can also enter each cash flow in a separate value box by pointing and clicking Using the NPV formula directly: Use the built-in Excel formula: NPV(rate, CF) Inputs: rate 0.07 cash flows 500 300 100 CF CF CF in year 1 CF in year 2 CF in year 3 CF 1. In cell H81 type: =NPVE 2. Then click on cell F83 or enter 0.07 and enter a comma 3. Then put your cursor on cell F84 (at the first CF of $500) and drag the cursor to F86 and enter the ending parenthesis and then click enter. 4. Your answer should be $810.95 Hint: This is similar to your calculator cash flow keys PV 5 3 Using the Table and PV formulas: 7 B Time CF 9 0 00 1 31 2 32 3 03 04 rate 05 08 PV SUM 07 08 Data read me Tutorial 3 1. Type in your cash flows in cells D99 through D102. 2. Type in your discount rate in cell D101 3. In cel E99 type: =PV(SDS101.C99,0, D99) 4. Copy to cells E100 E102 5. In cell E 106 type: SUM(E99 E102) 6. You should get $810.95 as the PV SUM with the inputs from the previous problem. CF,0 + Type here to search ED File Home Insert Page Layout Formulas Data Review View Help Acrobat Times New Roman - 10 X Cut Copy Format Painter ' ' 23 Wrap Text Paste BIU A Merge & Center $ - % 48.5 Clipboard 5 Font Alignment 5 Number V124 D f 4 A B C DEFIGH 91 J K L 4. Your answer should be $810.99 92 93 Hint This is similar to your caleulator cash flow keys 94 95 96 Using the Table and PV formulas: 97 98 CF PV 1. Type in your cash flows in cells D99 through D102 99 0 2. Type in your discount rate in cell DIO! 100 1 3. In cell E99 type PV($D$101.699,0,-D99) 101 2 4. Copy to cells E100-E 102 102 3 5. In cel E106 type: =SUM(E99 6102) 103 6. You should get $810 95 as the PV SUM with the 104 rate inputs from the previous problem. 105 CF, 0 106 PV SUM 107 10B 109 Test your skills 110 What is the present value of a cash flow of $1,000 today, $2,000 at the end of the next year (time1). 111 $3,000 at the end of year 2, and $4,000 at the end of year 3, discounted at 5% (rate 05)? 112 113 114 Your answer should be 3908 1.20 115 PRINT your spreadsheets. 116 End of Spreadsheet2 117 118 119 120 121 122 123 124 125 128 127 129 read me Tutorial Type here to search O A Neveve View Help Acrobat IEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protec B C D E F G H Tutorial 5. Future Values of Uneven Cash Flow Stream What you will learn: 1. How to find the future value of uneven cash flow streams 2. How to use an Excel table 3. How to write formulas and enter values into an Excel table Fill out the cells with boxes around them like this => PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safe to stay in Protected View A B C D E F G . Enter your name in this box K Tutorial 6 - Caleulating Loan Payments and Loan Amortization ou will determine the loan payment and amortization schedule for a fixed rate loan (such as a car loan or a house mortgage) Problem 1: You are planning to purchase a car that costs $20.000. You can secure a loan from a credit union. The loan -Eficer informed you of the loan terms. The annual percentage rate (APR) is 6%, and the loan must be repaid in 5 years. Vhat will be your monthly payments to the credit union? PVIFA is used to calculate loan paytices Finance Concept: PV - PMT PVIFA = PMT [1- (1/(1++))} PMT PV (11-(1/(1+r)b) We'invert the formula for PV of an annuity, and solve for payment or cash flows Numerical Solution: PVPMTPVIFA. 20,000 = PMT [1-(1/(1+06.06 20,000 - PMT [1-74726.06 20,000 PMT. 42124 PMT=20.000/42124 PMT= $4,747.88 with calculator In Words: The yearly payments on a $20,000 loan for 5 years at 6% are $4,747.93 by the Wizard (The difference is due to rounding errors Excel Solution for Monthly Payments: Note Interest Rate is formatted in percent by clicking on menu button -20000 Loan Amount Annual Interest Rate Term of Loan in Years Monthly Interest Rate 5 Enter monthly Interest Rate: E35/12 TO . read me Tutorial 6 Type here to search O CE View Help Acrobat O PROTECTED VIEW Be Careful-files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View En C86 K L A B D E F G H 28 by the Wizard. The difference is due to rounding errors 29 30 Excel Solution for Monthly Payments: 31 32 Note Interest Rate is formatted in percent by clicking on menu button 33 34 Loan Amount -20000 35 Annual Interest Rate 698 38 Term of Loon in Year's 5 37 Monthly Interest Rate Enter monthly Interest Rate: = E35/12 38 Term of Loan in Months Enter per in months: E36 *12 39 40 Woticed the interest rate to agree with the cumber of preds 41 Solutions 42 43 44 45 Monthly Payment Annual Amount Paid Total Amount Paid Interest Paid Over Term of Loan In cell F42 enter: PMT(E37,E38 E34), or use Par Wizard In cell E43 enter: E42 * 12 In cell E44 enter E43 E36 In cell E45 enter: =E44E34 (a (+) sign be 34 is negative) 47 Monthly Payments are calculated by the Excel Wizard Function, or by using the formula for 48 PMT with the inputs (interest term. principal). Check rumbers: Total Amt paid $23,199 49 50 Test Your Skills: 51 What is the monthly payment if you buy a $30.000 car, holding time and rate constant? 52 answer is $$79.98 53 54 Problem 2 You are planning to purchase a car that costs $20,000. You want to shop around and compare different loan rates and terms before you make a decision. Now you'll want to use a Table to compete your total interest and principal 55 so they can be compared 58 57 1. Amount 20000 58 Ance Interest Rate see table Step 1 In cell Dot enter -PART(C6V12 SES60, SE$57) 59 Ten of Leon in Years 5 Watch $ signs. Copy by draping the cursor 60 Total number of months 60 Excel function: PMT (rate, term, principal) 61 Divide the interest rate by 12 to convert to monthly 62 With Various Interest Rates: 63 Rate PMT Total Interest Interest - Principal read me Tutorial 6 A Type here to search O Search AutoSave Om aus. TVM 06 loan - Protected View File Home Insert Page Layout Formulas Data Review View Help Acrobat PROTECTED VIEW Be careful from the internet ca cortan Vese. Unless you need to ed asset to stay in Protected View 86 X Enable Eoling 1 A B C D E F G H You are planning to purchase a car that costs $20.000. You want to shop around and compare different loan rates and terms before you make a decision Now you'll want to use a Tave to compute your total interest and principal so they can be compared L M Lean Art -20000 Amal Interest Rate see the Tenn of Loan in Years Totalbet of months 60 Step 1.Inced Dot enter PMT(C6V 12.SE$60.SE$57) Watch $ signs Copy by draging the corner Excel functia PT = (rate, ter principal Divide the sterest rate by 12 to convert to monthly "Torres Interest Principal 2 With Various Interest Rates Rate PM 2.096 3096 4025 5.00% 6.0% 7.034 8.046 PM @ 2%= 351 In Prin-21033 Step 2 Tocal Interest Coaster D660-SES57dcopy down Step 3 amet paid Interest Pics In Guter E64-SES$7 and copy down 2 s With Varones (Years): Merly B Year PMI Total derest Interest Principal 7 Tapets rate 0.12 loan prin 20000 temin y 12 1911 11 PMT - (rate, term, principal) 12 -C012 yrs 12-20000) Staplete: -PATISK878 12.SK 80-11 SKS79) and copy down Step 2 L 172 1734C77"SKSSO-SK579 B 9 Step 3. In 27 E27 SKS79 0 check bers. 12 yrs. pert-$361, Int+P$37826 #1 Step 4 Copy down cells in table read me Tutorial Type here to search o c File Home Insert Page Layout Formulas Data Review View Help Acrobat PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. C86 X & B A B C D E F G . K B2 11 PMT=(rate, term, principal) 93 12 = (06/12 yrs *12,-20000) 84 B5 Step 1. In cell D77 enter: PMT(SKS78/12,5KS80-12,5KS79) and copy down. 86 87 Step 2. In E77 enter =D77*C77"SKS80+SKS79 8B 89 Step 3. In G77 enter =E77-SK879 90 check numbers: 12 yrs pet-$263, Int+Prin$37,826 91 Step 4. Copy down cells in table 92 93 What is your choice of loan rates and terms? 94 95 98 Problem 3: 97 You are planning to purchase a car for $20.000. You decide to get the loan from the credit union. It is for 696 annually but you decide to pay it off in 3 years You want to see the loan amortization table for your loan to see how the 98 interest and principal are being paid off Fill in the inputs and the table will automatically calculate for you. 99 100 Loan Enter the loan as-20000 101 Monthly PMT You must calculate ParT =(rate/12, term" 12. principal) 102 Rate alate (006) 103 Term years of loan (3) 105 106 Loan Amortization Table Constant PEC: S0.00 Principal 107 0 108 Month 109 110 2 111 112 4 113 114 6 115 116 117 9 Beg Principal Balance 0 0 0 0 Total Monthly PMC 0 O 0 0 0 Monthly Loan Interest 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Principal Reduction 0 0 0 0 0 0 0 0 0 Ending Principal Balance 0 0 0 0 O 0 0 0 0 read me T