Please do in excel. Thanks in advance!
1. Find the present value of a 5-year ordinary annuity of $100 with 10% APR and quarterly compounding. Requirement: Calculation and output sections must update automatically with changes in data input. Data Input In this problem a period is a Cash Flow / Period Discount Rate / Period Number of Periods ? Type your answer in B5. $100 10.38% 5 10.38% 0 $0.00 $0.00 1 $100.00 $90.60 2 $100.00 $82.07 3 $100.00 $74.36 4 $100.00 $67.36 5 $100.00 $61.03 Calculation using a time line approach Effective Discount Rate / Period Period Cash Flows Present Value of Cash Flows (PVCFS) Output Sum of all PVCFs Output with other methods Using Annuity Formula Using PV function $375.41 $375.41 $375.41 2. Find the values of an annuity due of $100 for 5 years with 10% APR and semi-annual compounding. Requirement: Calculation and output sections must update automatically with changes in data input. Data Input In this problem a period is a Cash Flow / Period Discount Rate / Period Number of Periods ? Type your answer in B26. $100 10.25% 5 Calculation using a time line approach Effective Discount Rate / Period Period Cash Flows Present Value of Each Cash Flow Sum of all PVCFs 10.25% 0 $100.00 $100.00 $415.28 1 $100.00 $90.70 2 $100.00 $82.27 3 $100.00 $74.62 4 $100.00 $67.68 5 $0.00 $0.00 Output Using PV function $415.28 1. Find the present value of a 5-year ordinary annuity of $100 with 10% APR and quarterly compounding. Requirement: Calculation and output sections must update automatically with changes in data input. Data Input In this problem a period is a Cash Flow / Period Discount Rate / Period Number of Periods ? Type your answer in B5. $100 10.38% 5 10.38% 0 $0.00 $0.00 1 $100.00 $90.60 2 $100.00 $82.07 3 $100.00 $74.36 4 $100.00 $67.36 5 $100.00 $61.03 Calculation using a time line approach Effective Discount Rate / Period Period Cash Flows Present Value of Cash Flows (PVCFS) Output Sum of all PVCFs Output with other methods Using Annuity Formula Using PV function $375.41 $375.41 $375.41 2. Find the values of an annuity due of $100 for 5 years with 10% APR and semi-annual compounding. Requirement: Calculation and output sections must update automatically with changes in data input. Data Input In this problem a period is a Cash Flow / Period Discount Rate / Period Number of Periods ? Type your answer in B26. $100 10.25% 5 Calculation using a time line approach Effective Discount Rate / Period Period Cash Flows Present Value of Each Cash Flow Sum of all PVCFs 10.25% 0 $100.00 $100.00 $415.28 1 $100.00 $90.70 2 $100.00 $82.27 3 $100.00 $74.62 4 $100.00 $67.68 5 $0.00 $0.00 Output Using PV function $415.28
