Question

I need formulas in excel: Thank you You're the owner of Arcata Scoop - the best ice cream shop on the planet. You're considering purchasing a new machine that will allow you to make chocolate dipped ice cream cones. The machine costs $42,000.
Details:
Depreciation:	The machine will be depreciated using straight line depreciation over 7 years, but you think that you'll only use the machine for 5 years. At the end of the 5th year, you'll retire the machine and sell it. Its estimated value at the end of year 5 is $6,000.
Extra sales & costs:	You estimate that you'll make $45,000 in extra sales each year but that your cost of goods sold will increase by $30,000 each year.
Cost of capital and taxes:	You use a cost of capital of 11% and have a 25% tax rate.
To Do:	Use NPV and IRR to evaluate this investment. Should you purchase the machine or not?
Machine cost			Sales			Useful Life
Depreciable life			COGS			Tax rate
Depreciation per year			Estimate sales price at end			Cost of capital
Net Income:			Terminal Value:
Sales			Sale price
Less: Cogs			Book value
Gross Profit			Taxable gain
Less: Depreciation			Tax
Taxable Income			After tax cash flow
Less: Income Tax
Net Income
Cash Flow:
Add back depreciation
Cash Flow
Time	Cash Flow
0
1
2
3
4
5
NPV
IRR

Answer 1

Machine cost = $42,000

Depreciable life = 7 years

Depreciation per year = 42000/7 = $6,000

Sales = $45,000

COGS = $30,000

Estimate sales price at end = $6,000

Useful Life = 5 years

Tax rate = 25%

Cost of Capital = 11%

Formulas Used:

Terminal Value:

Sale price = $6,000

Book Value = Purchase price - Accumulated Depreciation = 42,000 - (6,000 * 5) = $12,000

Taxable gain = 0

Tax = 0

After tax cash flow = $6,000

Formulas Used: