Question

Please include an Excel step by step process. Thank you

Big Sky Hospital plans to obtain a new MRI that costs $1.5 million and has an estimated four-year useful life. It can obtain a bank loan for the entire amount and buy the MRI, or it can obtain a guideline lease for the equipment. Assume that the following facts apply to the decision: - The MRI falls into the three-year class for tax depreciation, so the MACRS allowances are 0.33, 0.45, 0.15, and 0.07 in Years 1 through 4, respectively. - Estimated maintenance expenses are $75,000 payable at the beginning of each year whether the MRI is leased or purchased. - Big Sky's marginal tax rate is 40 percent. - The bank loan would have an interest rate of 15 percent. - If leased, the lease payments would be $400,000 payable at the end of each of the next four years. - The estimated residual (and salvage) value is $250,000. a. What are the NAL and IRR of the lease? Interpret each value. b. Assume now that the salvage value estimate is $300,000, but all other facts remain the same. What is the new NAL? The new IRR?

Answer 1

Answer a)

		Year 0	Year 1	Year 2	Year 3	Year 4
Cost of owning:		($1,500,000)
Net purchase price
Maintenance cost		($75,000)	($75,000)	($75,000)	($75,000)	$0
Maintenance tax savings			$30,000	$30,000	$30,000	$30,000
Depreciation tax savings			$198,000	$270,000	$90,000	$42,000
Residual value						$250,000
Tax on residual value						($100,000)
Net cash flow		$1,575,000	$153,000	$225,000	$45,000	$222,000
PV (net cash flow)		($1,575,000)	$133,043	$170,132	$29,588	$126,929
Cost of leasing:
Lease payment			($400,000)	($400,000)	($400,000)	($400,000)
Lease tax savings			$160,000	$160,000	$160,000	$160,000
Maintenance cost		($75,000)	($75,000)	($75,000)	($75,000)	$0
Maintenance tax savings			$30,000	$30,000	$30,000	$30,000
Net cash flow		($75,000)	($285,000)	($285,000)	($285,000)	($210,000)
PV (net cash flow)		($75,000)	($247,826)	($215,501)	($187,392)	($120,068)
Discounting rate		15%
Net advantage to leasing:
PV cost of leasing		($845,787.34)
PV cost of owning		($1,115,306.75)
NAL		$269,519.41
		Year 0	Year 1	Year 2	Year 3	Year 4
Net cash flow from buying		($1,575,000)	$153,000	$225,000	$45,000	$222,000
Net cash flow from leasing		$75,000	$285,000	$285,000	$285,000	$210,000
Net Cash flow		($1,500,000)	$438,000	$510,000	$330,000	$432,000
	IRR	5.59%

Answer b) Now with change in salvage value :

		Year 0	Year 1	Year 2	Year 3	Year 4
Cost of owning:		($1,500,000)
Net purchase price
Maintenance cost		($75,000)	($75,000)	($75,000)	($75,000)	$0
Maintenance tax savings			$30,000	$30,000	$30,000	$30,000
Depreciation tax savings			$198,000	$270,000	$90,000	$42,000
Residual value						$350,000
Tax on residual value						($140,000)
Net cash flow		$1,575,000	$153,000	$225,000	$45,000	$282,000
PV (net cash flow)		($1,575,000)	$133,043	$170,132	$29,588	$161,234
Cost of leasing:
Lease payment			($400,000)	($400,000)	($400,000)	($400,000)
Lease tax savings			$160,000	$160,000	$160,000	$160,000
Maintenance cost		($75,000)	($75,000)	($75,000)	($75,000)	$0
Maintenance tax savings			$30,000	$30,000	$30,000	$30,000
Net cash flow		($75,000)	($285,000)	($285,000)	($285,000)	($210,000)
PV (net cash flow)		($75,000)	($247,826)	($215,501)	($187,392)	($120,068)
Discounting rate		15%
Net advantage to leasing:
PV cost of leasing		($845,787.34)
PV cost of owning		($1,081,001.55)
NAL		$235,214.21
		Year 0	Year 1	Year 2	Year 3	Year 4
Net cash flow from buying		($1,575,000)	$153,000	$225,000	$45,000	$282,000
Net cash flow from leasing		$75,000	$285,000	$285,000	$285,000	$210,000
Net Cash flow		($1,500,000)	$438,000	$510,000	$330,000	$492,000
	IRR	6.99%