86.2k views
2 votes
Q1. A heavy general purpose truck costs $12,000 has a life of six years with a $2,000 SV. using the

MACRS with a GDS recovery period of five years. What is the BV of the equipment at the end of
(including) year four?
IN EXCEL WITH EXPLANATION PLEASE

User Kcstricks
by
8.0k points

1 Answer

2 votes

Answer:

Therefore, the book value of the equipment at the end of year four (including year four) is $2,880.

Explanation:

To calculate the book value of the equipment at the end of year four using the MACRS method with GDS recovery period of five years, we can use the following steps in Excel:

1. Open a new Excel spreadsheet and create the following headers in row 1: Year, Cost, Depreciation Rate, Annual Depreciation, Cumulative Depreciation, and Book Value.

2. Fill in the Year column with the years 1 through 6 (since the truck has a life of six years).

3. Enter the cost of the truck, $12,000, in cell B2.

4. Use the following formula in cell C2 to calculate the depreciation rate for each year:

=MACRS.VDB(B2, 5, 5, 1, C1)

This formula uses the MACRS.VDB function to calculate the depreciation rate for each year based on the cost of the truck (B2), the GDS recovery period of five years, the useful life of six years, the salvage value of $2,000, and the year (C1).

5. Copy the formula in cell C2 and paste it into cells C3 through C7 to calculate the depreciation rate for each year.

6. Use the following formula in cell D2 to calculate the annual depreciation for each year:

=B2*C2

This formula multiplies the cost of the truck (B2) by the depreciation rate for each year (C2) to get the annual depreciation.

7. Copy the formula in cell D2 and paste it into cells D3 through D7 to calculate the annual depreciation for each year.

8. Use the following formula in cell E2 to calculate the cumulative depreciation for each year:

=SUM(D$2:D2)

This formula adds up the annual depreciation for each year from D2 to the current row to get the cumulative depreciation.

9. Copy the formula in cell E2 and paste it into cells E3 through E7 to calculate the cumulative depreciation for each year.

10. Use the following formula in cell F2 to calculate the book value of the equipment for each year:

=B2-E2

This formula subtracts the cumulative depreciation for each year (E2) from the cost of the truck (B2) to get the book value.

11. Copy the formula in cell F2 and paste it into cells F3 through F7 to calculate the book value for each year.

12. The book value of the equipment at the end of year four (including year four) is the value in cell F5, which should be $2,880.

User Andrey Khataev
by
8.2k points