Question

3. As owner of the Big Tap you are considering an investment in upgrading the bar's entertainment systems. If you decide to invest in these upgrades you have estimated it will require an initial investment of $48,000 which will be used to buy some TVs, a digital jukebox, and a new stereo system for the bar. You estimate that this will boost revenues by $15,000 during the first year because of the additional customers this will attract to the bar. These additional revenues are expected to increase by 3% each year. However, the upgrade will also create additional costs of $5,000 in the first year due to additional satellite service, and music and sports package fees. These additional costs are also expected to increase over time, but only by 2% each year. Your accountant says you can depreciate the initial investment using the straight-line method over 6 years, and your marginal tax rate is 25%. You estimate that the new sound and video equipment will have zero salvage value so the full $48,000 will be depreciated.
d. Calculate the series of NATCFs and the NPV for this project at a 10% discount rate assuming that you finance the investment using a 7-year loan with a fixed interest rate of 6% (annual compounding and end-of-year payments) and a 50% down payment. Complete the final two columns of Table 1 below.

Hint: This will require you to adjust the NATCF calculations that you made for part a. You will need to account for the loan when calculating the NATCF series. You can assume the bar is able to deduct the interest portion of the loan payments as an expense for income tax purposes. This should be done using a similar table as you created for part a, adding columns for the loan payment information. Clearly label your tables to help with grading. An example layout for the NATCF table with the loan is provided on page

e. Finally, using NPV as your decision criteria and a 10% discount rate, should you upgrade the sound system in the bar and, if so, should you pay cash or use a loan to finance the investment? Very briefly justify/explain your answer.

Answer 1

		WITH LOAN
	Pv	Loan amount=48000/2	$24,000
	Rate	Interest Rate	6%
	Nper	Number of years of loan	7
	PMT	Annual Loan repayment	$4,299.24	(Using PMT function of excel with Rate=6%, Nper=7, Pv=-24000)
				A	B	C=A*6%	D=B-C	E=A-D
			Year	Beginning Loan Balance	Total Loan Repayment	Interest	Principal	Ending Loan Balance
			1	$24,000	$4,299.24	$1,440.00	$2,859.24	$21,140.76
			2	$21,140.76	$4,299.24	$1,268.45	$3,030.79	$18,109.96
			3	$18,109.96	$4,299.24	$1,086.60	$3,212.64	$14,897.32
			4	$14,897.32	$4,299.24	$893.84	$3,405.40	$11,491.92
			5	$11,491.92	$4,299.24	$689.52	$3,609.73	$7,882.20
			6	$7,882.20	$4,299.24	$472.93	$3,826.31	$4,055.89
			7	$4,055.89	$4,299.24	$243.35	$4,055.89	($0.00)
		967.25
			N	A	B	C	D	E	F=D+E	G=A-B-C-E	H=G*0.25	I=G-H-D+C	PV=I/(1.1^N)
			Year	Inflows	Outflows	Depreciation	Principal	Interest	Total Loan Repayment	Taxable Income	Taxes(25%)	Total after tax cash Flow	PV at 10% Discount
			0									($24,000)	($24,000)
			1	$15,000	$5,000	$8,000	$2,859.24	$1,440.00	$4,299.24	$560.00	$140.00	$5,560.76	$5,055.24
			2	$15,450	$5,100	$8,000	$3,030.79	$1,268.45	$4,299.24	$1,081.55	$270.39	$5,780.37	$4,777.17
			3	$15,913.50	$5,202	$8,000	$3,212.64	$1,086.60	$4,299.24	$1,624.90	$406.23	$6,006.03	$4,512.42
			4	$16,390.91	$5,306.04	$8,000	$3,405.40	$893.84	$4,299.24	$2,191.03	$547.76	$6,237.87	$4,260.55
			5	$16,882.63	$5,412.16	$8,000	$3,609.73	$689.52	$4,299.24	$2,780.95	$695.24	$6,475.99	$4,021.08
			6	$17,389.11	$5,520.40	$8,000	$3,826.31	$472.93	$4,299.24	$3,395.78	$848.94	$6,720.53	$3,793.56
			7	$17,910.78	$5,630.81		$4,055.89	$243.35	$4,299.24	$12,036.62	$3,009.15	$4,971.58	$2,551.20
			8	$18,448.11	$5,743.43					$12,704.68	$3,176.17	$9,528.51	$4,445.12
			9	$19,001.55	$5,858.30					$13,143.25	$3,285.81	$9,857.44	$4,180.52
			10	$19,571.60	$5,975.46					$13,596.14	$3,399.04	$10,197.11	$3,931.43
												SUM	$17,528
		NPV at 10% Discount	$17,528
		Yes, We should upgrade the system
		NPV is Positive
		Part a , b and c are shown in uploaded image
					Number Editi Clipboard Font Alignment Styles Cells forx IRR(H7:H17) X G23 В C D E F G H K L 3 NO LOAN 4 PV5=CF/(1.05^N) PV5=CF/(1.1^N) PV5=CF/(1.15^N) PV5=CF/(1.2^N) 5 CF Cash Flow PV@5% PV@10% PV@15% PV@20% 6 Year O($48,000.00) $9,500.00 ($48,000.00) $9,047.62 $8,854.88 ($48,000.00) $8,636.36 $8,068.18 ($48,000.00) $8,260.87 $7,381.85 $6,597.27 ($48,000.00) $7,916.67 7 8 1 $9,762.50 $6,779.51 9 2 $5,806.50 $4,973.79 $4,261.05 3 $10,033.63 $8,667.43 $7,538.41 10 $7,044.36 4 $10.313.65 $8,485.07 $5,896.86 $5,271.49 11 $8,307.61 $8,134.89 $6,545.36 5 $10,602.85 $6,583.54 12 6 $10,901.53 $6,153.63 $4,726.18 $4,713.03 $3,650.90 13 $9,209.98 $3,462.37 $2,570.34 14 7 $9,528.51 $6,449.27 $4,445.12 $3,114.89 $2,216.03 15 $1,910.44 $1,646.89 ($6,267.89) $9,857.44 $6,354.19 $4,180.52 $2,802.10 16 10 $10,197.11 $6,260.14 $3,931.43 $2,520.57 17 $29,106.45 $13,307.73 $2,021.31 18 SUM $29,106.45 NPV at 5% 19 NPV at 10% $13,307.73 20 $2,021.31 NPV at 15% 21 ($6,267.89) NPV at 20% 22 Internal Rate of Return 16.09% (Using IRR function of excel over the cash flow) 23 24