Home Loan Assessment Excel Model

One can check its home loan eligibility along with the PMAY Subsidy eligibility by entering some basic inputs.

by Rohit Vyas
Business Analyst at IDFC First Bank, MumbaiFollow

2 reviews251 views|Start the discussion!|Bookmark download for free

Description
The file also provides an automated Sanctioned letter, moratorium cum disbursement schedule and loan repayment schedule. This file is helpful for young credit managers who are not having experience in Home loan credit appraisal.

I have made certain assumptions while making the excel sheet. The lending companies or banks may be liberal in applying those assumptions while calculating the eligibility in their systems. I will explain the process flow of credit assessment along with the assumptions that I have considered. The bullet list of process flow is as under:
1. KYC & Income details
2. Property Details
3. Assessment Sheet
4. Credit Rating Module
5. EMI Calculation and Cost to the borrower
6. Sanction Letter
7. Timeline of Own Contribution and loan disbursement
8. Moratorium Period Schedule
9. Home Loan Repayment Schedule
10. PMAY Subsidy Calculation

Basic assumptions:
1. Yellow colour filled cells are editable.
2. In KYC and Income Sheet, please write zero while filling the details of income in case of no income and nil income.
3. Basically, there are two types of properties first one is ready to move / resale property and second is under construction property. This workbook is only based on the purchase of Ready to move & Re-sale Property-based transactions.
4. The PMAY subsidy part is automatically calculated while calculating eligibility loan eligibility and the repayment schedule includes the subsidy effect.
5. I have considered the period for Own contribution of three months before the date of sanction and one month for the sanction of the loan from the date of application. PMAY subsidy sanction period is considered for the three months from the date of sanction of home loan.

This Best Practice includes
Home Loan Assessment - KYC, Income details, Property Details, PMAY Subsidy, etc