Excel: Interest related functions

Learning to use interest related functions

From getting loans from creditors to analyzing investment opportunities, interest rates and growth rates are used extensively in finance. Like other functions you can calculate the interest rates using logarithmic operations and solve through multiple steps of algebra or you can use the relevant Excel functions to compute your answers.

For the purposes of this module you are expected to learn about the following interest rate$-$related functions:

$(1)$ DISC ; $(2)$ EFFECT ; $(3)$ NOMINAL; $(4)$ RATE

Based on your understanding of these functions, complete the following statements:

$$ The DISC function is designed to calculate the of a security.

$$ The EFFECT function is designed to calculate the $.$

$$ The NOMINAL function is designed to calculate $.$

$$ The RATE function is designed to calculate the interest rate required to $.($Note: Two correct answers are available for this question.$)$

Consider the following situations where you need to use one of the functions described earlier. Identify which function will be most appropriate to use in each scenario to calculate the relevant interest rate.

Compute the interest rate, or yield, earned by a non$-$coupon$-$paying $($discount$)$ bond that is purchased on June $1,2015$ and that matures on May $31,2017.$ Assume that the price per $$100$ face value of the bond is $$104,$ the redemption value per $$100$ of face value is $$100,$ and the basis is $0($US $($NASD$)30/360).$ Remember: You can express the bond$$s settlement and redemption dates either as a normal date $($e$.$g$.,6/01/2015$ enclosed in quotation marks$)$ or as a sequential integer value found using the DATEVALUE function. If you elect to use the sequential integer method, use the DATEVALUE to determine that the appropriate value for June $1,2015$ is $42156,$ and the corresponding date for May $31,2017$ is $42886.($Note: Round your answer to two decimal places.$)$

RATE

DISC

NOMINAL

EFFECT

A

B

$1$ Data Description

$2$ Settlement date $6/1/2015$

$3$ Maturity Date $5/31/2017$

$4$ Price $104$

$5$ Redemption value $100$

$6$ Basis $0$

$7$ Interest Rate $=$

$8\%$

Compute the interest rate at which $$500$ per month payments should grow to accumulate savings of $$100,000$ in $7$ years. Payments are made at the end of each month. $($Note: Round your answer to two decimal places.$)$

DISC

RATE

NOMINAL

EFFECT

A

B

$1$ Data Description

$2$ NPER $7$ x $12=84$

$3$ PMT $500$

$4$ PV $0$

$5$ FV $100,000$

$6[$Type$]0$

$7$ Interest Rate $=$

$8=\%$

Compute the stated interest rate on a promotional credit card if the effective interest rate is $8\mathrm{.}0\%.$ Assume that interest is compounded $($a$)$ semi$-$annually and $($b$)$ quarterly. $($Note: Round your answers to two decimal places.$)$

NOMINAL

RATE

EFFECT

DISC

A

B

$1$ Data Description

$2$ Compounded semi$-$annually

$3$ Interest Rate $=$

$4=\%$

$5$ Compounded quarterly

$6$ Interest Rate $=$

$7=\%$

$8$

Compute the actual interest rate paid or earned on a mortgage loan if the bank states that the nominal rate is $12\%,$ and the interest is compounded $($a$)$ semi$-$annually and $($b$)$ quarterly. $($Note: Round your answers to two decimal places.$)$

NOMINAL

RATE

EFFECT

DISC

A

B

$1$ Data Description

$2$ Compounded semi$-$annually

$3$ Interest Rate $=$

$4=\%$

$5$ Compounded quarterly

$6$ Interest Rate $=$

$7=\%$

$8$