Corporate Finance: Tutorial 1

Formula

Excel Formula

• $ Discount\ rate: RATE(nper, pmt, pv, fv) $
• $ Number\ of\ payments: NPER(rate, pmt, pv, fv) $
• $ Payment\ amount\ per\ period: PMT(rate, nper, pv, fv) $
• $ Present\ value: PV(rate, nper, pmt, fv) $
• $ Future\ value: FV(rate, nper, pmt, pv) $

  Note:
  When it comes to applying $PV(1+r)^t=FV$ formula in excel, either PV of FV has to be a negative value in order for another cash flow variable value to be a positive.

Math Formula

• Ordinary Annuity
  $$PVA_0=C \frac{1-\frac{1}{(1+r)^t}}{r}$$
  $$FVA_t=C \frac{(1+r)^t-1}{r}$$

  关于以上两个公式的推导，可以先从$FVA_t$开始推导，比较符合我们常人的生活经验思维：
  $FVA_t \times r = C(1+r)^t - C$，也就是利息 = 本息 - 本金
  因为$FV=PV(1+r)^t$，所以上面的公式两边同时除以$(1+r)^t$，就可以得到Present Value的公式
  $$\frac{FVA_t }{(1+r)^t} \times r = \frac{C(1+r)^t-C}{(1+r)^t} \Rightarrow PVA_0 \times r = C - \frac{C}{(1+r)^t} \Rightarrow PVA_0=C \frac{1-\frac{1}{(1+r)^t}}{r}$$

• Ordinary Perpetuity: $$PVP_0 = \frac{C}{r}$$
• Relationship between APR and EAR:
  $$1+EAR=(1+\frac{APR}{Number\ of\ Periods\ per\ Year})^{Number\ of\ Periods\ per\ Year}$$
  When compounding frequency is annual, $(1+\frac{APR}{1})^1 = 1+EAR$ or $1+APR = 1+EAR$.
  As number of periods per year ↑, EAR ↑ if APR remains the same
  $$1 + EAR = e^{APR}$$
  Number of periods per year → ∞, maximum EAR is achieved

Exercise 1 - 难度系数 *

Andy deposited $3,000 this morning into an account that pays 5 percent interest, compounded annually.
Barb also deposited $3,000 this morning into an account that pays 5 percent interest, compounded annually.
Andy will withdraw his interest earnings and spend it as soon as possible.
Barb will reinvest her interest earnings into her account. Given this, which one of the following statements is true?

A. Barb will earn more interest the first year than Andy will.
B. Andy will earn more interest in year three than Barb will.
C. Barb will earn interest on interest.
D. After five years, Andy and Barb will both have earned the same amount of interest.
E. Andy will earn compound interest.

Andy is earning simple interest. 
Barb is earning compound interest. 
In the first year, both will earn the same amount of interest given the same interest rate and initial investment amount $3,000. 
But after the first year, Barb will always earn more interest than Andy.

EXERCISE 2 - 难度系数 *

Louis is going to receive $20,000 six years from now.
Soo is going to receive $20,000 nine years from now.
Which one of the following statements is correct if both Louis and Soo apply a 7% discount rate to these amounts?

A. The present values of Louis and Soo’s monies are equal.
B. In future dollars, Soo’s money is worth more than Louis’ money.
C. In today’s dollars, Louis’ money is worth more than Soo’s.
D. Twenty years from now, the value of Louis’ money will be equal to the value of Soo’s money.
E. Soo’s money is worth more than Louis’ money given the 7 percent discount rate.

Although both Louis and Soo are going to receive the same amount $20,000, 
but they are not comparable as they are not received at the same point of time. 
$1 today worths more than $1 tomorrow (general rule). 
$20,000 received six years from today should be also worth more than $20,000 received nine years from now. 
Given 7% discount rate, both the PV and FV of Louis’s $20,000 are always higher than the PV and FV of Soo’s $20,000 at the same point of time on the timeline.

EXERCISE 3 - 难度系数 **

You invested $1,650 in an account that pays 5% simple interest.
How much more could you have earned over a 20-year period if the interest had compounded annually?

A. $849.22
B. $930.11
C. $982.19
D. $1,021.15
E. $1,077.94

Simple interest = $1,650 + ($1,650 x .05 x 20) = $3,300 
Annual compounding = $1,650 x (1.05)^20 = $4,377.94 
Difference = $4,377.94 − $3,300 = $1,077.94

Excel Calculation:
FV(rate, nper, pmt, pv) = FV(5%, 20, 0, -1650) = $4,377.94

Exercise 4 - 难度系数 **

At 11% interest rate, how long would it take from today to quadruple your $1,500 if it is invested 3 years later from now?

A. 13.09 years
B. 13.28 years
C. 13.56 years
D. 16.28 years
E. 16.56 years

$6,000 = $1,500 x (1 + .11)^t
t = 13.28 years
t + 3 = 16.28 years

Excel Calculation:
NPER(rate, pmt, pv, fv) = NPER(11%,0,1500,-1500*4) = 13.2832

Self-Study Exercise 1 - 难度系数 **

You would like to give your daughter $75,000 towards her college education 17 years from now. How much money must you set aside today for this purpose if you can earn 8% on your investments?

A. $18,388.19
B. $20,270.17
C. $28,417.67
D. $29,311.13
E. $32,488.37

Present value = $75, 000 x [1/(1 + .08)^17 ] = $20,270.17

Excel:
PV(rate, nper, pmt, fv) = PV(8%,17,0,-75000) = $20,270.17

Self-Study Exercise 2 - 难度系数**

Fourteen years ago from now, your parents set aside $7,500 to help fund your college education.
2 years later from now, that fund is valued at $26,180. What rate of interest is being earned on this account?

A. 7.99 percent
B. 8.13 percent
C. 8.51 percent
D. 9.34 percent
E. 10.06 percent

$26,180 = $7,500 x (1 + r)^16 
r = 8.13%

Excel: 
RATE(nper, pmt, pv, fv) = RATE(16,0,7500,-26180)

Exercise 5 - 难度系数***

Which one of the following statements is correct given the following two sets of project cash flows?

A. The cash flows for Project B are an annuity, but those of Project A are not.
B. Both sets of cash flows have equal present values as of time zero given the same positive discount rate.
C. The present value of Project A cannot be computed because the second cash flow is equal to zero.
D. As long as the discount rate is positive, Project B will always be worth less today than will Project A.

Explanation

• Option A: Both Project A and B cash flows are not annuity as the cash flow each period in A is not of same amount, and the cash flow each period in B is also not of same amount.
• Option B, C, D: You can change the RATE to any positive interest rate (assume that the rate = 5% ) to prove the options make sense or not. Project A should worth more than Project B given the same positive interest rate because a higher proportion of A’s total cash flows occurs earlier than B’s. Even project A and B have the same amount of cash flows totally ($11,000), the distribution of the cash flows in each year is not the same. Given the same interest rate, they will not have the same present value. If the cash flow is zero, then the PV must be always zero.
  Excel formula: PV(rate, nper, pmt, fv)

  	PV of A	PV of B
  Year 1	PV(5%, 1, 0, -6000) = $5,714.29	PV(5%, 1, 0, -2000) = $1,904.76
  Year 2	$0	PV(5%, 2, 0, -3000) = $2,721.09
  Year 3	PV(5%, 0, 3, -2500) = $2,159.59	PV(5%, 0, 3, -3000) = $2,591.51
  Year 4	PV(5%, 0, 4, -2500) = $2,056.76	PV(5%, 0, 4, -3,000) = $2,468.11
  Sum	$9,930.64	$9,685.47

Exercise 6 - 难度系数***

Which of the following statements related to interest rates are correct?
I. Annual percentage rates consider the effect of interest earned on reinvested interest payments. 年利率考虑利息收入对再投资利息支付的影响
II. When comparing loans, you should compare the effective annual rates. 在比较贷款时，你应该比较有效的年利率
III. Lenders are required by law to disclose the effective annual rate of a loan to prospective borrowers. 法律规定放款人须向准借款人披露贷款的有效年利率
IV. Annual percentage rates(APR) and effective interest rates(EAR) are equal when interest is compounded annually. 当利息按年复利时，年利率和实际利率相等

A. I and II only
B. II and III only
C. II and IV only
D. I, II, and III only
E. II, III, and IV only

Explanation
Statement I:
APR(annual percentage rates) is an annualized interest rate using simple interest.
再投资利息这个说的是复利，年利率考虑的是单利

Statement II:
APRs with different compounding frequency are not comparable.
We should convert all APRs with different compounding frequency into EARs(effective annual rates) for comparison.

Statement III:
Lenders are required by law to disclose披露 APRs, not EARs.

Statement IV:
Relationship between APR and EAR: (参考自课件)
$$1+EAR=(1+\frac{APR}{Number\ of\ Periods\ per\ Year})^{Number\ of\ Periods\ per\ Year}$$
When compounding frequency is annual, $(1+\frac{APR}{1})^1 = 1+EAR$ or $1+APR = 1+EAR$.
As number of periods per year ↑, EAR ↑ if APR remains the same
$1 + EAR = e^{APR}$
Number of periods per year → ∞, maximum EAR is achieved

Exercise 7

You are the beneficiary of a life insurance policy.
The insurance company informs you that you have two options for receiving the insurance proceeds.
You can receive a lump sum of $200,000 today,
or receive payments of $1,400 a month for 20 years.
You can earn 6% on your money.
Which option should you take and why?

A. You should accept the payments because they are worth $209,414 to you today.
B. You should accept the payments because they are worth $247,800 to you today.
C. You should accept the payments because they are worth $336,000 to you today.
D. You should accept the 200,000USD because the payments are only worth 189,311USD to you today.
E. You should accept the 200,000USD because the payments are only worth 195,413USD to you today.

Explanation:
Option 1 is a $200,000 lump sum payment received today while option 2 is a annuity with monthly payments last for 20 years. We need to compare the PVs of the two options to see which one is more valuable today.
$$ APV = \$1400 \times \frac{1-(1+\frac{0.06}{12})^{20 \times 12}}{\frac{0.06}{12}} = \$195,413 $$

Excel:
$$PV(rate, nper, pmt, fv) = PV(0.06 / 12, 20*12, -1400, 0)=195,413.08$$
Since PMT is a monthly payment, NPER has to be in months and RATE has to be monthly rate.

EXERCISE 8

You are buying a previously owned car today at a price of $3,500.
You are paying $300 down in cash and financing the balance for 36 months at 8.5 percent.
What is the amount of loan payments in each quarter?

A. $101.02
B. $112.23
C. $303.06
D. $304.92
E. $336.69

Explanation
PV: 3500-300=3200 (Since you contribute $300 using your own money, you indeed borrow $3200 today.)
NPER: 36 months
Rate: 8.5% / 12 (8.5% is an APR, usually interest rate given is APR unless otherwise stated.)
PMT in months: PMT(rate, nper, pv, fv) = PMT(8.5%/12, 36, -3200) = $101.02
Loan payment in each quarter: $303.05

Note:
1. Since PMT is a monthly payment, NPER has to be in months and RATE has to be monthly rate.
2. when it comes to applying $PVA=C \frac{1-\frac{1}{(1+r)^t}}{r}$ formula in excel, either PVA or C has to be a negative value in order for another cash flow variable value to be a positive.

EXERCISE 9 - 难度系数**

Today, you borrowed $6,200 on your credit card to purchase some furniture.
The interest rate is 14.9 percent, compounded monthly.
How long will it take you to pay off this debt assuming that you are not charged anything else and make regular monthly payments of $120?

A. 5.87 years
B. 6.40 years
C. 6.93 years
D. 7.23 years
E. 7.31 years

Explanation:
$$PVA_0=C \frac{1-\frac{1}{(1+r)^t}}{r}$$
$$ 6200 = 120 \times (\frac{1-1/(1+\frac{0.149}{12})^t}{\frac{0.149}{12}})$$
$$t=83.14months→/12→6.93years$$

Excel:
NPER(rate, pmt, pv, fv) = NPER(14.9%/12,120,-6200)/12 = 6.93

EXERCISE 10

Your insurance agent is trying to sell you an annuity that costs $200,000 today.
By buying this annuity, your agent promises that you will receive payments of $1,225 a month for the next 30 years.
What is the annual rate of return without compounding on this investment?

A. 0.48 percent
B. 0.52 percent
C. 5.20 percent
D. 6.20 percent
E. 6.38 percent

Explanation:
$$200000 = 1225 \times (\frac{1-(1/(1+\frac{r}{12})^{30\times12}}{\frac{r}{12}})$$
RATE(nper, pmt, pv, fv)=RATE(30*12,1225,-200000)=0.5167%
APR(annual percentage rate) = 0.5167% * 12 = 6.2%

EXERCISE 11

You have $5,600 that you want to use to open a savings account.
There are three banks located in your area.
The rates paid by banks A through C, respectively, are given below. Which bank should you select for savings?
A. 3.27 percent, compounded annually
B. 3.25 percent, compounded semi-annually
C. 3.10 percent, compounded continuously

Explanation:
Relationship between APR and EAR:
$$1+EAR=(1+\frac{APR}{Number\ of\ Periods\ per\ Year})^{Number\ of\ Periods\ per\ Year}$$
When compounding frequency is annual, $(1+\frac{APR}{1})^1 = 1+EAR$ or $1+APR = 1+EAR$.
As number of periods per year ↑, EAR ↑ if APR remains the same
$$1 + EAR = e^{APR}$$
Number of periods per year → ∞, maximum EAR is achieved

Different APRs in option A, B, and C are not comparable, we need to convert different APRs(annual percentage rate) into EAR(effective annual rate) for comparison purpose.

• EAR of A: $(1+3.27\%/1)^1 = 1 + EAR \rightarrow EAR = 3.27\%$
• EAR of B: $(1+3.25\%/2)^2 = 1 + EAR \rightarrow EAR = 3.2764\%$
• EAR of C: $e^{3.10\%} = 1+EAR \rightarrow EAR = 3.1486\%$
  显然，B选项是最大的

SELF-STUDY EXERCISE 3

What is the value of $600 paid twice per year for 40 years at 8 percent return after all the payments made immediately?

A. $301,115
B. $306,492
C. $310,868
D. $330,747
E. $347,267

Explanation:
You are asked about the future value of an annuity cash flow stream with all $600 semiannual payments once they are all paid.
FV(rate, nper, pmt, pv)=FV(8%/2,40*2,-600)=$330,746.99

SELF-STUDY EXERCISE 4

Your local travel agent is advertising an upscale winter vacation package for travel 3 years from now to Antarctica.
The package requires that you pay $25,000 today,
$30,000 one year from today,
and a final payment of $45,000 on the day you depart three years from today.
What is the cost of this vacation in today’s dollars if the discount rate is 9.75 percent?

A. $89,695
B. $86,376
C. $91,219
D. $91,407
E. $93,478

Explanation:
$$ PV = \$25,000 + \frac{\$30,000}{(1 + 0.0975)^1} + 0 + \frac{\$45,000}{(1 + 0.0975)^3} = \$86,376 $$
Excel: PV(rate, nper, pmt, fv)

SELF-STUDY EXERCISE 5

You grandfather won a lottery years ago.
The value of his winnings at the time was $50,000.
He invested this money such that it will provide annual payments of $2,400 a year to his heirs forever.
What is the rate of return?

A. 4.75 percent
B. 4.80 percent
C. 5.00 percent
D. 5.10 percent
E. 5.15 percent

Explanation:
Ordinary Perpetuity: $PVP_0 = \frac{C}{r}$
The cash flow pattern is a perpetuity as annual payments last forever.
$$r = \frac{C}{PVP} = \frac{\$2,400}{\$50,000} = 4.80percent $$

SELF-STUDY EXERCISE 6

Your credit card company charges you 1.65 percent interest per month.
What is the quoted interest rate on your account?

A. 18.95 percent
B. 19.80 percent
C. 20.90 percent
D. 21.25 percent
E. 21.70 percent

Explanation:
Quoted interest rate is APR=(periodic interest rate) x (number of periods in a year)
APR = 0.0165 x 12 = 19.80 percent.

SELF-STUDY EXERCISE 7

The Pawn Shop loans money at an annual rate of 21% and compounds interest weekly.
What is the actual rate being charged on these loans?

A. 23.16 percent
B. 23.32 percent
C. 23.49 percent
D. 23.56 percent
E. 23.64 percent

Explanation:
Actual rate is EAR.
$$(1+\frac{APR}{number\ of\ periods\ in\ a\ year})^{number\ of\ periods\ in\ a\ year} = 1+EAR $$
$$EAR = (1 + \frac{0.21}{52})^{52} - 1 = 23.32\% $$