module 3-5

Question 1

NPV

Answer 1

NPV is the present value of all the cash flows relating to a project including the initial investment which is normally a negative value

Question 2

IRR

Answer 2

is the percentage return that discounts all cash flows from a project, including the initial investment, to zero.

Question 3

XNPV

Answer 3

Returns the net present value for a schedule of
cash flows that is not necessarily periodic

= XNPV(.15, B25:B30, A25:A30)

a is the cost of capital

b is the range of cash flows

c is the range of dates corresponding to the cash
flow

Question 4

XIRR

Answer 4

Returns the IRR for a schedule of cash flows
that is not necessarily periodic.

= XIRR(B4:B10, A4:A10, 0)

a is the range of cash flows

b is the range of dates corresponding to the
cash flows

c is the initial guess of the IRR

Question 5

YEILD

Answer 5

– returns the yield on a security that pays periodic interest

YIELD(settlement, maturity, rate, pr, redemption,
frequency, [basis])

Settlement- The security’s settlement date.

Maturity- The security’s maturity date

Rate- The security’s annual coupon rate.

Pr- The security’s price per $100 face value.

Redemption- The security’s redemption value per $100 face value.

Frequency- The number of coupon payments per year.

Question 6

PRICE

Answer 6

Returns the price of a bond

= PRICE(A1, A2, .05, .08, 100, 2)

a is the start date if the bond

b is the maturity date of the bond

c is the coupon rate

d is the bonds YTM

e is the redemption amount per $100 of face value

f is the number coupon payments per year

Question 7

Goal Seek

Answer 7

– calculates the precise solution to a problem that would otherwise require trial & error

– select data tab, what if analysis, goal seek, click cell containing formula that will return the result you want, enter goal in the To Value box, enter cells to change in the By Changing Cell box.

Question 8

Solver

Answer 8

– better than goal seek as it is more flexible, it remembers what it did before, can set up conditions, needs to be uploaded in ADD-INS

Question 9

FV

Answer 9

– calculates the future value of an annuity

Question 10

PV

Answer 10

– calculates the present values of an annuity

Question 11

NPER

Answer 11

– calculates the number of periods to repay a loan given a fixed repayment

Question 12

PMT

Answer 12

– calculates the periodic payment to pay off a loan

Question 13

IPMT

Answer 13

– calculates the interest payment for a given period

Question 14

PPMT

Answer 14

– calculates the payment on the principle for a given period

Question 15

CUMIPMT

Answer 15

– calculates the cumulative interest paid on a loan between start & finish

Question 16

CUMPRINC

Answer 16

calculates the cumulative principle paid on a loan between start & finish

Question 17

RATE

Answer 17

– calculates the internal rate of return of an annuity stream (all cash flows equal & payments at beginning of period