Finance Chapter 4 - Questions 1 to 39 Discounted Cash Flow Valuation

Excel File

Chapter 4	1 - 39
1)	Simple vs Compound Interest
		r	0.07
		c	5000
		t	10
		Simple Interest	8500	=D4*D3*D5+D4
		Compound Interest	$9,835.76	=FV(D3,D5,,-D4)
		Difference	$1,335.76	=D7-D6
2)	Future Values
a)		C	3550
		t	10
		r	0.06
		FV	$6,357.51	=FV(D17,D16,,-D15)
b)		C	3550
		t	10
		r	0.08
		FV	$7,664.18	=FV(D22,D21,,-D20)
c)		C	3550
		t	20
		r	0.06
		FV	$11,385.33	=FV(D27,D26,,-D25)
3)	Present Values
		Years	interest	FV	PV
		9	0.07	15451	$8,404.32	=PV(D34,C34,,-E34)
		13	0.09	51557	$16,816.79	=PV(D35,C35,,-E35)
		16	0.14	886073	$108,890.97	=PV(D36,C36,,-E36)
		24	0.11	550164	$44,951.14	=PV(D37,C37,,-E37)
4)	Interest Rate
		PV	Years	FV	Rate
		217	3	293	10.53%	=RATE(D42,,-C42,E42)
		432	10	1053	9.32%	=RATE(D43,,-C43,E43)
		41000	16	162181	8.97%	=RATE(D44,,-C44,E44)
		54382	19	483500	12.19%	=RATE(D45,,-C45,E45)
5)	Periods
		PV	Rate	FV	Years
		625	0.06	1284	12.36	=NPER(D49,,-C49,E49)
		810	0.09	4341	19.48	=NPER(D50,,-C50,E50)
		18400	0.07	234162	37.60	=NPER(D51,,-C51,E51)
		21500	0.1	215000	24.16	=NPER(D52,,-C52,E52)
6)	Periods
		Rate	0.0575
		How long to double?
		PV	1
		FV	2
		NPER	12.39807798	=NPER(D56,,-D58,D59)
7)	Present Value
		FV	540000000
		NPER	20
		Rate	0.056
		PV	-$181,599,812.54	=PV(D66,D65,,D64)
8)	Rates of Return
		PV	1680000
		FV	1100000
		NPER	3
		Rate	-13%	=RATE(D73,,D71,-D72)
9)	Perpetuities
		PMT	80
		Rate	0.026
		PV	3076.92	=D78/D79
10)	Continuos Compounding
		FV = PVe^rt (E is euler number, use EXP in Excel)
		PV	Years	Rate	FV
		1625	5	0.14	3272.35	=C86*EXP(E86*D86)
		1625	3	0.06	1945.48	=C87*EXP(E87*D87)
		1625	10	0.08	3616.50	=C88*EXP(E88*D88)
		1625	8	0.09	3338.45	=C89*EXP(E89*D89)
11)	PV Multiple Cash Flows
		Years	1	2	3	4
	Discount Rate		585	815	1630	2140
		0.05	$557.14	$739.23	$1,408.06	$1,760.58
		0.13	$517.70	$638.26	$1,129.67	$1,312.50
		0.18	$495.76	$585.32	$992.07	$1,103.79
			=PV($C$100,D96,,-D97)
12)	PV Mulitple Cash Flows
		X			y
		PMT	4850		PMT	6775
		NPER	9		NPER	5
		Rate A	0.05		Rate A	0.05
		Rate B	0.21		Rate B	0.21
		Rate A PV	-$34,472.94		Rate A PV	-$29,332.20
		Rate B PV	-$18,941.36		Rate B PV	-$19,823.54
		Investment X for rate a, investment y for rate B (in this example)
13)	Annuity Present Value
		PMT	5500
		R	0.075
		Periods	15	40	75
		Value	-$48,549.16	-$69,269.25	-$73,009.99	=PV($D$120,F121,$D$119)
			Perpetual	(C/r)
			73,333.33	=D119/D120
14)	Calculating Perpetuity Values
		C	18000
		R	0.043
		PV	418,604.65	=D130/D131
		PV	445000
		Rate?	0.040449438	=D130/D134
		PV = C/R
		R = C/PV
15)	Calculating EAR
		APR	Compound
		0.098	Quarter	0.101660685	=EFFECT(C143,4)
		0.124	Monthly	0.131295813	=EFFECT(C144,12)
		0.076	Daily	0.078954038	=EFFECT(C145,365)
		0.084	Infinite	0.087628894	=EXP(C146)-1
	EAR continuous - eq-1
16)	Calculating APR
		Compound	EAR	APR
		Semi Annual	0.104	0.101428086	=NOMINAL(D153,2)
		Monthly	0.089	0.085563448	=NOMINAL(D154,12)
		Weekly	0.116	0.109866765	=NOMINAL(D155,52)
		Infinite	0.154	0.143234168	=LN(1+D156)
		Infinite compounding ( APR = ln(1 + EAR))
17)	Calculating EAR
			Bank A	Bank B
		Rate	0.157	0.162
		Compound	Monthly	Semi Annual
		EAR	0.1688	0.1686	=EFFECT(E162,2)
18)	Interest Rates
		PMT	10
		periods	12
		Discount %	0.1
		Discount$	12
		Price	108
				(Note - need to ensure beginning period selected)
		Interest Rate	1.98%	=RATE(D170,D169,-D174,,1)
		APR	102.77%	=D176*52
		EAR	176.68%	=EFFECT(D177,52)
19)	Caluclating number of periods
		PMT	450
		Rate	0.013	month
		PV	18700
		NPER	60.16	=NPER(D183,-D182,D184)
20)	Calculating EAR
		PV	3		FV=PV(1+r)
		FV	4		r=FV/PV - 1
		Rate	0.333333333	=D191/D190-1
		APR	1733%	=D192*52
		EAR	313916515.69%	=EFFECT(D193,52)
21)	Future Value
		PV	1500
		Rate	0.072
		NPER	6
		Compounding		FV
		Annual	1	$2,276.46	=FV($D$201/D205,$D$202*D205,,-$D$200)
		Semi annual	2	$2,293.02	=FV($D$201/D206,$D$202*D206,,-$D$200)
		Monthly	12	$2,307.52	=FV($D$201/D207,$D$202*D207,,-$D$200)
		Continuously	--	$2,310.50	=D200*EXP(D201*D202)
22	Simple Vs Compound Interest
		Simple Rate	0.074
		Nper	10
					simple rate = (1 + r)Nper -1
		Total Simple interest over  10 years			0.74	= (1 + r)Nper -1
					1.74	=(1+r)10
			Compound Rate		5.70%
23	Calculating Annuities
		Nper	30
		PMT	750	Stock
		PMT	325	Bond
		Stock Return APR	0.105
		Bond APR	0.061
		Compound	Monthly
		After 30 NPER	Sum Both
		APR	0.069
		Nper	25
		PMT	?
	Step 1	Calculate FV for Bond and Stocks
		Stocks	$1,887,300.74	=FV(D226/12,D223*12,-D224)
		Bonds	$332,782.27	=FV(D227/12,D223*12,-D225)
		Total	$2,220,083.01	=SUM(D237:D238)
	Step 2	PMT	-$15,549.74	=PMT(D231/12,D232*12,D239)
24	Calculating rates of return
		To triple return
		PV	1
		FV	3
		Nper	12
		Return per quarter	31.61%	=RATE(D248/3,,-D246,D247)
25	Calculating rates of return
			A	B
		PV	55000	55000
		Nper	5	11
		FV	105000	235000
		Rate	13.81%	14.11%	=RATE(E258,,-E257,E259)
26	Growing Perpetuities
		FV	210000		PV = C/(r - g)
		Nper	3
		Growth	0.025
		Discount Rate	0.11
		Step 1 - PV of cash at Nper -1
			2,470,588.24	=D265/(D268-D267)
		PV	$2,005,184.84	=PV(D268,2,,-D271)
27	Perpetuities
		Dividend	1.75
		Quarterly			PV = C / r
		R	0.055
		Quarterly R	0.014	=D279/4
		PV	127.27	=D277/D280
28	Annuity PV
		PMT	5700
		Receve in	3
		For Nper	25
		Discount	0.068
		Payments	23
		PV of 22 years	$65,363.72	=PV(D289,D290,-D286)
		PV	57,305.23	=D291/(1+D289)^(D287-1)
		(need to reduce t by one)
29	Annuity Present Values
		NPER	15
		PMT	825
		Start	6
		Rate 1	0.09
		Rate 2	0.12
		PV1	-$6,650.07	=PV(D301,D298,D299,)
		PV2	3773.42715	=-D304/((1+D302)^(D300-1))
30	Balloon Payments
		Cost of Purchase	825000
		Down	0.2
		Amount Borrowed	660000
		APR	0.054
		Years	30
		Periods	360
		PMT	-$3,706.10	=PMT(D314/12,D316,D313,,0)
		Balloon Period	8
		pv	$571,856.86	=PV(D314/12,D316-(D320*12),D317)
31	Calulating Interest Expense
		Rate 1	0.019
		Nper	6	months
		Rate2	0.16
		Nper	6
		PV	7500
		Rate 1 Period	-$7,571.53	=FV(D326/12,D327,,D330)
		Rate 2 Period	$8,197.81	=FV(D328/12,D329,,D332)
		Interest	$697.81	=D333-D330
32	Perpetuities
		PV	1650000		PV=C/r
		PMT	185000		r=C/PV
		Rate?	0.112121212	=D341/D340
			11.21%
33	Growing Annuity
		Cost	135000
		PMT	38000
		growth	0.055
		nper	5
		reqr return	0.11
	What is the PV of the cash flows?			C {[1/(r – g)] – [1/(r – g)] × [(1 + g)/(1 + r)]t}
		PV =	38000	18.18181818	18.18181818	0.775617152
		PV	155,028.15
		Compare cost to PV
34
		PV	75000
		deposit /5 salary	0.1
		rate	0.095
		salary g	0.034
		Nper	35
		C	7500
				C {[1/(r – g)] – [1/(r – g)] × [(1 + g)/(1 + r)]t}
	Start with next year deposit		7755	16.39344262	16.39344262	0.134500744
		PV	110,031.91	=D366*(E366-F366*G366)
		FV	-$2,636,409.31	=FV(D361,D363,,D367,0)
35	Present Value and Interest Rates
		Nper	15
		PMT	5250
		Rate 1	0.1	-$39,931.92	=PV(D375,$D$373,$D$374)
		Rate 2	0.05	-$54,493.20	=PV(D376,$D$373,$D$374)
		Rate 3	0.15	-$30,698.69	=PV(D377,$D$373,$D$374)
36	Calculating the number of payments
		PMT	190
		Rate	0.0875
		FV	25000
		Nper	92.58554581	=NPER(D382/12,-D381,,D383)
37	Calculating Annuity Present Value
		PV	105000
		PMT	2025
		nper	60
		Rate?	0.49%	=RATE(D391,-D390,D389)
		APR	5.90%	=D392*12
38	Calculating Loan Payments
		Nper	30	Years
		PV	225000
		APR	0.051
		PMT	-875
		PV PMT	$161,156.71	=PV(D399/12,D397*12,D400)
		Balance Remaining	$63,843.29	=D398-D402
		FV Balance Remain	-$293,885.25	=FV(D399/12,D397*12,,D403)
39	Present and Future Values
		Total PV	5800
		Rate	0.08
		Year	Cash Flow	PV
		1	1300	$1,203.70	=D411/(1+$D$408)^C411
		2
		3	1900	$1,508.28	=D413/(1+$D$408)^C413
		4	2450	$1,800.82	=D414/(1+$D$408)^C414
			Sum:	$4,512.81	=SUM(E411:E414)
			PV Cash2	$1,287.19	=D407-E415
			FV Cash2	$1,501.38	=-FV(D408,C412,,E418)