Question

Overview

Mary has been working for a university for almost 25 years and is now approaching retirement. She wants to address several financial issues before her retirement and has asked you to help her resolve the situations below.

Issue B:

Mary has been working at the university for 25 years, with an excellent record of service. As a result, the board wants to reward her with a bonus to her retirement package. They are offering her $75,000 a year for 20 years, starting one year from her retirement date and each year for 19 years after that date. Mary would prefer a one-time payment the day after she retires. What would this amount be if the appropriate interest rate is 7%?

Instructions

Create a spreadsheet or other document, calculating part B of the assignment, and provide a brief explanation for each component including its significance.

Answer 1

Annual Interest Rate(r) 7%

Number of year left to receive NPER  20

Annual Receipts Expected (PMT) is 75,000

Lumpsum receipt at the end, (FV) is Zero

The future value of 20 payments of $75,000 at the end of each year for 20 years at 7%/yr is:
FV = PMT * ((1+r) ^ t) - 1) / r = 75000 * ((1+0.07) ^ 20) - 1) / 0.07 = $3,074,661.92

The present value of the above amount is:
PV = FV / (1+r) ^ t = 3074661.92 / (1+0.07) ^ 20 = $794,551.07
. . . which is the appropriate one-time payment.

therefore, Mary would prefer a one time payment the day after she retires is $794,551(rounded off)

Book1 - Microsoft Excel (Product Activation Failed) - 2 X File Home Insert Page Layout Formulas Data Review View - x * Cut Calibri 11 AA = = = r General Wrap Text Merge & Center - B I U - A $ % 48. Cell Styles Insert Delete Format Conditional Format Formatting as Table Styles Copy Paste Format Painter Clipboard E6 A B Σ AutoSum - A Fill- Sort & 2 Clear Filter Editing Find & Select Alignment Number Cells Font fc (REF: NOTE) D F G H I J K L M N O P Q 20 75000 Annual Interest Rate(r) 7% Number of year left to receive NPER 20 Annual Receipts Expected (PMT) is 75,000 Lumpsum receipt at the end, (FV) is Zero Annual cash flow type Present value of given cash flow 0 =0 is received at the end of each year;1 received at the beginning of each year =PV(E3, E4, E5, (REF: NOTE) Sheet1 Sheet2 Sheet3 Sheet4 Ready BOU 100% 0- 1 Type here to search Bê e o of 9 ENG 6:39 PM IN 2/3/2020