Evaluation of employee group housing offer

A Pharma company has recently recruited 4 scientists at an average age of 27 and is looking to develop quite a few pharmacological formulations. With a view to retain them the company proposes to offer a housing scheme to them on the following terms and conditions:

1. The number of beneficiaries will be four in number

2. The total cost of purchasing apartments will be Rs.1 crore

3. PNBHFL offers an 18 year term @ 9.00% interest and HDFC offers 15 years term @ 8% interest.

4. The company will pay the housing finance company on an EAV basis.

5. A Down payment of 5% of the cost of the apartment has to be made and recoverable from the employee in 24 installments. The upfront payment will be made by the company.

6. In case of availing the loan from HDFC, 50% of the EAV will be recovered from the employee on a monthly basis and in case of availing the loan from PNBHFL 45% will be recovered from the employee. The option of choosing the service rests with the company only.

7. Assume a 6% accrual on the deductions taken from the employee under either option with quarterly compounding.

8. Assume that employee deductions happen on the first day of the month and housing loan payments made by the company happens on the last day of the year.

9. The housing loan is offered on a fixed interest basis and EAV will not change.

10. Assume the individual apartments are of equal value.

PVIFA 18, 9= 8.76 PVIFA 15, 8 =8.56

You are called upon to do the following:

1. Calculate the two EAV options and choose the best one.

2. Calculate monthly deduction of each employee under the two options

3. Calculate the accumulated maturity value per year inclusive of compounded quarterly interest.

4. Calculate the EAV differential borne by the company a. on annual basis b. over the tenor.

Hints for developing the solution

A. Develop loan amortization tables for 1 person for both loan options

B. Deduct the appropriate employer contribution in annual value- (eav )and divide by 12 to get monthly emi

C. Calculate effective rate of interest for quarterly compounding of emi deducted

D. Calculate annual realization of investment (multiplied) by 4 and compare with best option EAV

Show detailed tabulated workings using excel