Question

5(a) Three mutually exclusive new designs for an aircraft engine are under consideration. The engine has a ten-year life. The first engine incurs a cost of $1.2 million for a net saving of $300,000 per annum as compared to an existing engine. The second design would cost $1.5 million for a net saving of $400,000 per annum. The third has a cost of $2.1 million for a net savings of $500,000 per annum. Use MARR of 20%. Use IRR and NPW methods and find the best alternative. Use total investment and incremental investment approaches. 5(b) A $1000 utility bond with 14 years remaining before maturity can now be purchased for $780. It pays interest of $20 each 6-month period. What rate of return is earned by purchasing the bond at the current market price plus a brokerage charge of $20?

Answer 1

5-
PVAF At 20% for 10 Years	1-(1+r)^-n/r	1-(1.20)^-10 / 20%	.8384944/20%	4.192472
Net present value of option-1	cost of engine+(annual savings*PVAF at 20% for 10 Years)	-1200000+(300000*4.19247)	57741
Net present value of option-2	cost of engine+(annual savings*PVAF at 20% for 10 Years)	-1500000+(400000*4.19247)	176988
Net present value of option-3	cost of engine+(annual savings*PVAF at 20% for 10 Years)	-2000000+(400000*4.19247)	-3765
Year		Option 1 cash flow		Option 2 cash flow		Option 3 cash flow
0		-1200000		-1500000		-2100000
1		300000		400000		500000
2		300000		400000		500000
3		300000		400000		500000
4		300000		400000		500000
5		300000		400000		500000
6		300000		400000		500000
7		300000		400000		500000
8		300000		400000		500000
9		300000		400000		500000
10		300000		400000		500000
IRR =Using IRR function in MS excel	IRR(J1139:J1149)	21.41%	IRR(L1139:L1149)	23.41%	IRR(N1139:N1149)	19.95%
Incremental cash flow approach	Incremental cash flow between option 2 & 1
Year	cash flow option 1	Cash flow Option 2	Incremental cash flow
0	-1200000	-1500000	-300000
1	300000	400000	100000
2	300000	400000	100000
3	300000	400000	100000
4	300000	400000	100000
5	300000	400000	100000
6	300000	400000	100000
7	300000	400000	100000
8	300000	400000	100000
9	300000	400000	100000
10	300000	400000	100000
Incremental NPV =Using NPV funtion in MS excel			119247.2086
Incremental IRR =Using IRR function in MS excel			31.11%
As Incremental IRR is more than required rate of return and incremental NPV is greater than zero option 1 would be dropped
Incremental cash flow approach	Incremental cash flow between option 2 & 3
Year	Cash flow Option 2	cash flow option 3	Incremental cash flow
0	-1500000	-2100000	-600000
1	400000	500000	100000
2	400000	500000	100000
3	400000	500000	100000
4	400000	500000	100000
5	400000	500000	100000
6	400000	500000	100000
7	400000	500000	100000
8	400000	500000	100000
9	400000	500000	100000
10	400000	500000	100000
Incremental NPV =Using NPV funtion in MS excel		NPV(20%,K1172:K1181)+K1171	-180752.7914
Incremental IRR =Using IRR function in MS excel		IRR(K1171:K1181)	10.56%
As incremtnal NPV and IRR in case of option 2 & option 3 is negative and IRR is less than required rate of return of 20% so option 3 would be dropped and 2 would be finally selected for Investment.
5- Semi annual rate of return =Using rate function in MS excel	rate(nper,pmt,pv,fv,type) nper = 14*2 =28 pmt = 20 pv = -780-20 =-800 fv = 1000 type =0	RATE(28,20,-800,1000,0)	3.08%
Annaual rate of return			6.16

C. - = 1 D 3 AutoSum" AT A Eg Copy Paste Cell Styles Insert Delete Format 2 Clear New Microsoft Office Excel Worksheet (2) - Microsoft Excel Home Insert Page Layout Formulas Data Review View Cut Calibri - 11 - A A Dr Wrap Text Percentage BI U PA Format Painter Merge & Center Conditional Format Formatting as Table Clipboard Font Alignment Number Styles J1150 fc =IRR(J1139:J1149) н Net present value of option cost of engine+(annual savings*PVAF at 20% for 10 Years) | -1200000+(300000*4.19247) Net present value of option cost of engine+(annual savings*PVAF at 20% for 10 Years) | -1500000+(400000*4.19247) Net present value of option cost of engine+(annual savings*PVAF at 20% for 10 Years) | -2000000+(400000*4.19247) Sort & Filter Editing Find & Select Cells NO 57741 176988 -3765 Year Option 1 cash flow 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 -1200000 300000 300000 300000 300000 300000 300000 300000 300000 300000 300000 21.41%|IRR(L1139:L1149) Option 2 cash flow - 1500000 400000 400000 400000 400000 400000 400000 400000 400000 400000 400000 23.41% IRR(N1139:N1149) Option 3 cash flow -2100000 500000 500000 500000 500000 500000 500000 500000 500000 500000 500000 19.95% IRR =Using IRR function in MIRR(J1139:J1149) 1151 1152 1153 Incremental cash flow apprd Incremental cash flow between option 2 & 1 1154 Year cash flow option 1 1155 1156 1157 1158 Ke Sheet3 Sheet1 Sheet2 Select destination and press ENTER or choose Paste Cash flow Option 2 - 1200000 300000 300000 300000 Incremental cash flow - 1500000 -300000 400000 100000 400000 100000 400000 100000 BO 100% 0 . E B 2/23/2020

C. - = New Microsoft Office Excel Worksheet (2) - Microsoft Excel Eg Copy Paste Font Home Insert Page Layout Formulas Data Review View Cut Calibri - 11 - A A Dr Wrap Text Percentage WD - Autosum - 4 A A Format Painter BI E U E EF Merge & Center - $ % 70.00 , 8:09 Conditional Format Conditional Format Cell Cell Insert Delete Format Sort & Insert Delete Format Find & Formatting as Table Styles 2 Clear Filter Select Clipboard Alignment Number Styles Cells Editing N1150 f =IRR(N1139:N1149) н Net present value of option cost of engine+(annual savings*PVAF at 20% for 10 Years) | -1200000+(300000*4.19247) 57741 Net present value of option cost of engine+(annual savings*PVAF at 20% for 10 Years) | -1500000+(400000*4.19247) 176988 Net present value of option cost of engine+(annual savings*PVAF at 20% for 10 Years) | -2000000+(400000*4.19247) -3765 Year Option 1 cash flow 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 -1200000 300000 300000 300000 300000 300000 300000 300000 300000 300000 300000 21.41% IRR(L1139:L1149) Option 2 cash flow Option 3 cash flow - 1500000 -2100000 400000 500000 400000 500000 400000 500000 400000 500000 400000 500000 400000 500000 400000 500000 400000 500000 400000 500000 400000 500000 23.41% IRR(N1139:N1149) | 19.95% IRR =Using IRR function in MIRR(J1139:J1149) 1151 1152 Incremental cash flow apprd Incremental cash flow between option 2 & 1 Year cash flow option 1 1153 1154 1155 1156 1157 1158 ™ Ready Cash flow Option 2 - 1200000 300000 300000 300000 Incremental cash flow - 1500000 -300000 400000 100000 400000 100000 400000 100000 K Sheet3 Sheet1 Sheet2 | | | | | 100% = 0 - Il P d 8:56 AM 2/23/2020

Glan- New Microsoft Office Excel Worksheet (2) - Microsoft Excel Insert Page Layout Formulas Data Review View Home Cut Dr Percentage 1 D 3 AutoSum" AT Wrap Text 1 Merge & Center A Eg Copy Paste Insert Delete Format Calibri - 11 - A A BI U PA Font f EIRR(K1155:K1165) Cell Styles Conditional Format Formatting as Table Styles Format Painter Clipboard K1167 - 2 Clear Sort & Find & Filter - Select Editing Alignment Number Cells O н IRR =Using IRR function in MIRR(11139:J1149) M 23.41% IRR(N1139:N1149) N 19.95% 21.41% IRR(L1139:L1149) 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 ™ Ready Incremental cash flow apprd Incremental cash flow between option 2 & 1 Year cash flow option 1 Cash flow Option 2 Incremental cash flow - 1200000 - 1500000 -300000 300000 400000 100000 300000 400000 100000 300000 400000 100000 300000 400000 100000 300000 400000 100000 300000 400000 100000 300000 400000 100000 300000 400000 100000 300000 400000 100000 10 300000 400000 100000 Incremental NPV =Using NPV funtion in MS excel 119247.2086 Incremental IRR =Using IRR function in MS excel 31.11% As Incremental IRR is more than required rate of return and incremental NPV is greater than zero option 1 would be dropped Incremental cash flow apprd Incremental cash flow between option 2 & 3 Year Cash flow Option 2 cash flow option 3 Incremental cash flow -1500000 -2100000 -600000 400000 500000 100000 400000 500000 100000 400000 500000 100000 Sheet1 Sheet2 K Sheet3 | | | | | 100% = 0 - Il P d 8:57 AM 2/23/2020

C. - = New Microsoft Office Excel Worksheet (2) - Microsoft Excel D D - } ? Autosum - A7 Ea Copy Home Insert Page Layout Formulas Data Review View Cut Calibri - 11 - A A Dr Wrap Text Percentage Paste BI U PA 1 Merge & Center Conditional Format Cell Insert Delete Format Format Painter Sort & Find & Formatting as Table Styles 2 Clear Filter Select Clipboard Font Alignment Number Styles Cells Editing K1183 F IRR(K1171:K1181) NO 1162 300000 400000 100000 1163 300000 400000 100000 1164 300000 400000 100000 1165 10 300000 400000 100000 1166 Incremental NPV =Using NPV funtion in MS excel 119247.2086 1167 Incremental IRR =Using IRR function in MS excel 31.11% 1168 As Incremental IRR is more than required rate of return and incremental NPV is greater than zero option 1 would be dropped 1169 Incremental cash flow apprd Incremental cash flow between option 2 & 3 1170 Year Cash flow Option 2 cash flow option 3 Incremental cash flow 1171 -1500000 -2100000 -600000 1172 400000 500000 100000 1173 400000 500000 100000 1174 400000 500000 100000 1175 400000 500000 100000 1176 400000 500000 100000 1177 400000 500000 100000 1178 400000 500000 100000 1179 400000 500000 100000 1180 400000 500000 100000 1181 10 400000 500000 100000 1182 Incremental NPV =Using NPV funtion in MS excel NPV(20%,K1172:K1181)+K1171 -180752.7914 1183 Incremental IRR =Using IRR function in MS excel IRR(K1171:K1181) 10.56% 1184 As incremtnal NPV and IRR in case of option 2 & option 3 is negative and IRR is less than required rate of return of 20% so option 3 would be dropped and 2 would be finally selected for Investment. 1185 1186 5- Semi annual rate of returiratenper,pmt,pv, fv,type) nper = 14*2 =28 pmt = 20 pv = -7 RATE(28,20,-800,1000,0) 3.08% We Sheet3 Sheet1 Sheet2 Ready 90 100% u 8:58 AM . E B 2/23/2020

C. - = New Microsoft Office Excel Worksheet (2) - Microsoft Excel D } ? Autosum - A7 Eg Copy Paste 1171 1175 Home Insert Page Layout Formulas Data Review View Cut Calibri - 11 - A A Dr Wrap Text Percentage D - BI U D- -A E S E E Merge & Center - $ - % , 40.00 8.09 Conditional Format Conditional Format Painter Format Cell Cell Insert Delete Format Insert Delete Format Sort & Find & Formatting as Table Styles 2 Clear Filter Select Clipboard Font Alignment Number Styles Cells Editing K1186 fx =RATE(28,20,-800,1000,0) NO 1167 Incremental IRR =Using IRR function in MS excel 31.11% 1168 As Incremental IRR is more than required rate of return and incremental NPV is greater than zero option 1 would be dropped 1169 Incremental cash flow apprIncremental cash flow between option 2 & 3 1170 Year Cash flow Option 2 cash flow option 3 Incremental cash flow -1500000 -2100000 -600000 1172 400000 500000 100000 1173 400000 500000 100000 1174 400000 500000 100000 400000 500000 100000 1176 400000 500000 100000 1177 400000 500000 100000 1178 400000 500000 100000 1179 400000 500000 100000 1180 400000 500000 100000 400000 500000 100000 1182 Incremental NPV =Using NPV funtion in MS excel NPV(20%,K1172:K1181)+K1171 -180752.7914 1183 Incremental IRR =Using IRR function in MS excel IRR(K1171:K1181) 10.56% 1184 As incremtnal NPV and IRR in case of option 2 & option 3 is negative and IRR is less than required rate of return of 20% so option 3 would be dropped and 2 would be finally selected for Investment. 1185 1186 5- Semi annual rate of returratenper,pmt,pv,fv,type) nper = 14*2 =28 pmt = 20 pv = -7 RATE(28,20,-800,1000,0) 3.08% 1187 Annaual rate of return 6.16 1188 1189 1190 1191 ► Sheet3 Sheet1 Sheet2 Ready 90 100% - 0 mooooo 1181 - Il P d 9:00 AM 2/23/2020