---

Overview

Formulas
Task 1
Task 2
Task 3
Task 4
Task 5
Task 6

---

Sheet 1: Formulas

Annuity

Value of Annuity, given Present Value

Value of Annuity, given Future Value

Future Value of Annuity

Future Value of Annuity Due

Present Value of Annuity

Present Value of Annuity Due

Present Value of Perpetuity

---

Sheet 2: Task 1

Task 1

Find the monthly annuity which you will receive through 15 years, by having 3000 PLN on your deposit, 5 years before getting the first payment.

Monthly interest rate at 15%.

Solution:

1. At first, find the future value of your deposit, after 5 years:

PV

3000

n

5

m

12

r

1.25%

monthly

equation:

6,321.54 zł

function:

6,321.54 zł

2. Compute single payment:

PVAn

6,321.54 zł

r

1.25%

monthly

n

15

m

12

equation:

88.48 zł

function:

88.48 zł

---

Sheet 3: Task 2

Task 2

Find the amount of money, which you should pay for your deposit, to receive 5000 PLN of half-year annuity due after 5 years, through 20 years.

Bank uses half-yearly interest at 6%.

Solution:

1. At first, compute present value of annuity due:

PMT

5000

r

6%

half-yearly

n

20

m

2

equation:

79,745.37 zł

function:

79,745.37 zł

press 1 in the last cell of this funtion to make annuity due

2. Then compute the present value of your deposit:

FV

79,745.37 zł

r

6%

half-yearly

n

5

m

2

equation:

44,529.40 zł

function:

44,529.40 zł

---

Sheet 4: Task 3

Task 3

Your grandfather is going to pay 20 000 PLN for a bank deposit, with effective rate at 10%.

He would like to make it enough for a scholarship for you, in the amount of 500 PLN monthly (ordinary annuity) by five years of study.

Is it possible? If not, what advice can you give to grandfather?

Solution:

1. Find the nominal interest rate, equivalent to the effective one:

ref

10%

r nom

9.57%

annually

2. Then check, how many scholarships can you get:

PVAn

20000

r

9.57%

annually

n

4.03044549144401

m

12

equation:

PMT

500.000000927279

Use "Goal seek"

set c23 to 500 by changing c20

function:

PMT

500

n

4.03044550055732

divide my 12 because it will be valu in number of months. If we divide by 12 it will be number of years

Answer:

It's not enough!!!

3. In that case, how much should your grandfather pay at the beginning on this deposit?

PMT

500

r

9.57%

annually

n

5

m

12

equation:

23,769.25 zł

function:

23,769.25 zł

4. Or maybe bank should offer better effective rate?

PVAn

20000.0000086666

<-- instead of 20000 use a formula

PMT

500

r

17.27%

annually

set PVan to 20000 by changin cell c47

n

5

m

12

equation:

ref

18.71%

Use "Goal seek"

function:

r

17.27%

Multiply by 12 in order to make it an annual rate

ref

18.71%

5. What about reducing the amount of scholarship?

PVAn

20000

r

9.57%

annually

n

5

m

12

equation:

PMT

420.71 zł

function:

PMT

420.71 zł

---

Sheet 5: Task 4

Task

A brand new television costs 800 PLN. We can buy it in installments by paying for 2 years at the end of each quarter, 140 zł.

The second eventuality is to take a bank loan in the amount of 800 PLN, which will bear interest of 7% per quarter and will be repaid in equal quarterly installments over two years.

Which option is more profitable?

Solution:

loan

installments

PV

800

FV

0

n

2

m

4

quarterly r

7%

8.15%

loan

is/are more profitable

PMT

133.97 zł

installments

is/are more profitable

---

Sheet 6: Task 5

Task

The client of the bank wants to change quarterly ordinary annuity to monthly one. Bank offers interest rate at 12% quarterly and quartlerly payment in the amount of 1500 PLN.

Find the monthly annuity.

Solution:

1. At first, find the nominal, monthly interest rate:

Nominal, annual interest rate:

48.00%

for quarterly deposit

Effective interest rate:

57.35%

Nominal, annual interest rate:

46.20%

for monthly deposit

monthly:

3.85%

2. Then, compute the present value of annuity for quarterly payments...:

PMT

1500

r

48.00%

annually

n

1/4

<-- 1/4 because we are trying to change 1 quarterly payment and divide it into 3 seperate paymetns… and how many quarter's do we have in a year?

m

4

equation:

PVAn

1,339.29 zł

function:

PVAn

1,339.29 zł

3. … to finally find the single payment of new, monthly annuity:

PVAn

1,339.29 zł

r

3.85%

monthly

n

1/4

<- 1/4 because if we multiply 12 by this we will have 3 seperate payments per quarter

m

12

equation:

PMT

481.24 zł

function:

PMT

481.24 zł

---

Sheet 7: Task 6

Task

The Bank intends to provide to the University an annual payment of 50 000 zł for the purpose of development of scientific research.

What amount must be paid to the bank by sponsor, to ensure uninterrupted payments to the university? Bank offers deposit at 10% annually.

Solution:

PMT

50000

r

10%

PVP

500,000.00 zł