Question

*Excel formulas needed* A U.S.-based firm is considering a six- year project in Colombia. The following information is available about the project: Initial investment. The initial investment of USD 750,000 is used to purchase capital equipment. This equipment will be depreciated straight line to zero. At the end of six years, the remaining equipment will be sold for Colombian Peso (COP) 25,000,000. Working capital. The investment in working capital is COP 100,000,000. There are no changes in working capital until the end of the project when the full amount is recovered. Units, price, and costs. The firm will produce 2000 units of a product annually. The selling price is expected to be COP 599000 in the first year. This price is expected to increase at a rate of 4 percent annually. The direct expense per unit is expected to be COP 200000 in the first year. This is expected to increase at a rate of 5 percent annually. Indirect expenses are expected to be COP 50,000,000 annually. Taxes and miscellaneous. Colombian taxes on income and capital gains are 34 percent. There are no additional withholding taxes. All cash flows are repatriated when generated, and there are no additional U.S. taxes. The parity conditions are assumed to hold between Colombia and the United States. The relevant inflation indexes indicate a rate of 3 percent for the United States and 6 percent for Colombia. Spot USDCOP equals 3300. Brady’s USD denominated WACC is 12 percent.

b. What is the appropriate COP discount rate? Calculate the project NPV.

EXCEL FORMULAS only please. It will be incorrect if not run through Excel.

Thanks in advanced!

Answer 1

If the interest rate parity condition holds,with the forward rate R after 2 year:

(1+3%)*3300=(1+6%)*F or F= 3206.60

Similarly, the forward rate for subsequent years are as follows:  

Year	Rate
0	3,300.00
1	3,206.60
2	3,115.85
3	3,027.67
4	2,941.98
5	2,858.71

Year	Initial Investment	Working Capital	Units of production	Price(increment @4%/year)	Direct Expense per unit (increment @5%/year)	Indirect expense	Gross Profit (i.e. unit*(Price-Direct expense)-Indirect expense-Working Capital)	Depreciation	Tax	Cash Flow	Rate	Cash Flow in USD	Present Value (i.e. Cash Flow/1.12^year)
0	-2475000000	-100000000	0	0	0	0	-2575000000	0	0	-2575000000	3,300.00	(780,303.03)	(780,303.03)
1	0	0	2000	599000	200000	-50000000	748000000	-412500000	-114070000	633930000	3,206.60	197,695.15	176,513.52
2	0	0	2000	622960	210000	-50000000	775920000	-412500000	-123562800	652357200	3,115.85	209,367.28	166,906.31
3	0	0	2000	647878.4	220500	-50000000	804756800	-412500000	-133367312	671389488	3,027.67	221,751.48	157,838.32
4	0	0	2000	673793.536	231525	-50000000	834537072	-412500000	-143492604	691044468	2,941.98	234,891.13	149,277.56
5	0	0	2000	700745.2774	243101.25	-50000000	865288054.9	-412500000	-153947939	711340116	2,858.71	248,832.19	141,194.07
6	0	0	2000	728775.0885	255256.3125	-50000000	897037552.1	-412500000	-164742768	732294784	2,777.81	263,623.33	133,559.78
												NPV (Total of Present Value)	144,986.53

NPV in COP= 144986.53*3300=478455549 COP

So, COP discount rate is the IRR of the following cash flow:

So, equivalent COP discount rate is 22.55% and NPV of the project is $144,986.53