Excel Notes 1 PDF

Preview

Summary

excel notes...

Description

ACST1001 Excel Notes 1 1

EFFECT and NOMINAL

You should have idea1 about how these rates work, and how they differ: • nominal annual rate im , compounding m times a year • effective periodic rate imm • effective annual rate (EAR or i1 ) The latter 2 rates are effective. What is the difference between them?   1 im i12 applies to of a year so • effective periodic rate applies for exactly one month m m 12 • effective annual rate applies for exactly one year The following are all true:   im m −1 1. i1 = 1 + m 1 im = (1 + i1 ) m − 1 2. m   1 3. im = m × (1 + i1 )m − 1 In Excel, the EFFECT() and NOMINAL() functions can be used to calculate the EAR and nominal im rates respectively. • =EFFECT(nominal rate,npery) takes the nominal rate im and compounding frequency m as inputs, and gives the EAR as the output. E.g. if you are told the interest rate is 6% per annum compounding monthly, you can calculate the EAR using =EFFECT(6%,12). • =NOMINAL(effect rate,npery) takes the EAR and an integer m as inputs, and gives the corresponding nominal rate im as an output. E.g. if you are told the interest rate is 8% per annum effective, then =NOMINAL(8%,4) would give you the value of i4 . The image below is for Exercises 1 and 2:

The rest of the spreadsheet is empty. Exercise 1 Using =EFFECT() and/or =NOMINAL() functions, calculate the EAR in cell B3 and the nominal annual rate compounding daily in cell B4 that correspond to the value of i2 shown in cell B1. 1

If not, see Lectures 2 and 3

ACST1001 Excel Notes 1

1

Exercise 2 By changing the value in cell B1, verify that for equivalent interest rates, as m increases, im decreases.

2

PV and FV

Excel’s PV() and FV() functions can be used to calculate the PV/FV of • a single cash flow, or • an ordinary annuity/annuity due. The details for this are: • =PV(rate,nper,pmt,[fv],[type]) calculates the PV of cash flows at time “0” using the following 5 inputs: im m nper: number of periods - this is the number of payments n for annuity but just the length of interval n for single cash flow. pmt: level cash flow for an annuity, 0 if you’re dealing with a single cash flow [fv]: single cash flow at time n (excluding any level payment), if left blank Excel will assume the value is 0 [type]: can enter either 0 (the default value if omitted) = ordinary annuity, or 1 = annuity due (does not matter for single cash flow)

◦ rate: effective periodic rate ◦ ◦ ◦ ◦

• =FV(rate,nper,pmt,[pv],[type]) calculates the FV of cash flows at time “n” using the following 5 inputs: im m nper: number of periods - this is the number of payments n for annuity but just the length of interval n for single cash flow. pmt: level cash flow for an annuity, 0 if you’re dealing with a single cash flow [pv]: single cash flow at time 0 (excluding any level payment), if left blank Excel will assume the value is 0 [type]: can enter either 0 (the default value if omitted) = ordinary annuity, or 1 = annuity due (does not matter for single cash flow)

◦ rate: effective periodic rate ◦ ◦ ◦ ◦

Note: The signs of fv, pmt, and pv are important, as these Excel functions are set up in a specific way2 . 2

This can be tricky, and will be explained in lectures

ACST1001 Excel Notes 1

2

Exercise 3 Using the =PV() and/or =FV() functions, re-attempt the Student Exercises in the week 2 lecture. Play around with changing the signs of the inputs of the function to try and get a positive answer.

Exercise 4 Using the =PV() and/or =FV() functions, re-attempt both the Examples and the Student Exercises in the week 3 lecture. Play around with changing the signs of the inputs of the function to try and get a positive answer.

ACST1001 Excel Notes 1

3...