VARDHAN CONSULTING ENGINEERS

Financial Modeling and

Analysis of 50 Flats
Housing Project in
Gurgoan, Haryana IN

Project Finance Modeling and Analysis

Nehal Sharma

Christ university

Internship report – 1st may 2021 – 30 June 2021

 ACKNOWLEDGEMENT

The report was written as part of the VCE-Internships module assignment. It is based
on the analysis and financial modeling of a 50-flat housing project in Gurgoan,
Haryana. This report was written for the internship topic of Project Finance and
Modeling.

First and foremost, I'd want to express my gratitude to VCE-Internships for providing a
platform for me to learn about project finance and modeling. This has provided me with
the opportunity to learn about project finance or project analysis, as well as an
understanding of how to construct a financial model.

Finally, I'd like to thank my mentor, Mr. Ashish Kumar, CEO of Vardhan Consulting
Engineers, for giving guidance throughout this project via live interaction sessions.

I'd want to express my gratitude to Miss Neha Kumari for providing me with this
opportunity.

It’s a pleasure to do an internship program at Vardhan Consulting Engineers. The

knowledge and experience I gained during the project was great.

NEHAL SHARMA

2
 ABSTRACT

Gurgoan (formally gurugam) is a city in Haryana, India's northernmost state. It's about 30
kilometers southwest of the national capital, New Delhi, on the Delhi-Haryana border. In Gurgoan,
there are architecturally significant structures in a variety of styles and time periods. With
contemporary planning, the city has become home to a number of tall structures. Gurgoan's
assessment was with contemporary planning, the city has become home to a number of tall
structures. A total of 1100 residential towers were planned at Gurgoan. A good condominium in
Gurgoan costs at least $160,130 for a 93 square meter (1,000 sq ft) two bedroom (10,000,000).
 EXECUTIVE SUMMARY
VCE is a consulting firm formed by a group of engineers with excellent academic backgrounds and
decades of managerial expertise gained through working in a variety of organizations around the
world. Clients' complicated engineering, managerial, and financial challenges are addressed by
VCE.

Energy projects benefit from our technical and project management expertise. Solar PV power
projects (Utility Scale Large Scale Projects), and Pyrolysis projects, in particular (Plastic to Oil).

Our service includes;

 Financial Analysis, Feasibility Analysis, and Detailed Project Report (IM). For project finance,
financial closure can be achieved by debt or private equity.

 On-site and off-site services are available. Documentation and research

 EPC-Management Services and Project Management ransaction Services for Project Sale

 At NTP, we develop projects and transfer rights.

Our main consultants have worked in the energy sector for over ten years in India, the Philippines, the
United Kingdom, Cambodia, and Thailand. We specialize in solar PV projects and offer custom
engineering and management solutions to meet the demands of our clients. VCE has a variety of income
streams and business perspectives, including:
 Consulting in engineering and management.
 Pearl Jewellery Importing and Branding
 Cross-Currency Trading and the Stock Market Advisory services for insurance and
investments.
We've seen a solid growth in all of our businesses over the last three years, and we expect
significant returns on all of our investments.stakeholders. Apart from the companies stated above,
VCE also believes in the importance of society in the development of our country's future.
giving back to the community
1. Vardhan Merit Scholarship-

In this initiative, we pay scholarship to poor under-privileged girls and pay their entire
educational expense till class 12th. So that, financial burden never be the reason for
them to drop out from schools.

It also encourages the kids to study with more focus. Currently, we grant Rs. 50,000 (Fifty
Thousand Rupees) per year for this. We believe, this amount is going to increase in future.
(Amount Spent so far, Rs. 3.00 Lakhs+).

2. Internships and Training

In this initiative, we select students from various engineering and management colleges and
provide them internship and training. Our internships and training are very unique in nature
and it’s especially for the students of Core Engineering Sector (Electrical, Mechanical, Civil
and Energy Engineering) and Finance Management for preparing them to corporate / industry
ready. We provide them mentor from the industry. All this is done without taking any fee /
favor from the students. Currently, we grant Rs. 60,000 (Sixty Thousand Rupees) per year for
this. Majority of the amount will be paid as stipend to the best interns as encouragement.
(Stipend Paid so far, Rs. 1.50 Lakhs+).

The internship opportunity provides by vce has given all its interns a chance to improve their
skills and to gain theoretical as well as practical knowledge as per the individual specific
project titles and internship requirements.

My topic of my internship is Project Finance and Modeling. The modules and tasks that were
provided in the project are very helpful and knowledgeable. The initial tasks were so basic
they helped interns or me to gain the basic knowledge of the project finance and the idea of
preparing a financial model and to do financial analysis. It helps us to know the
terminologies and different aspects of project finance.

While, completing this internship with VCE, I have learned many new things and gained
many things and skills. This last module of this internship is very helpful in learning how to
make analysis and prepare a financial model. This is a very helpful tool in field of finance.

This report will be helpful in knowing the structure of residential and housing industry in
India, the scope for investing in residential projects, schemes and policies, and overall analysis
of residential; growth in india and state of housing in Gurgoan, Haryana, IN.
 TABLE OF CONTENTS

CHAPTER-1: INTRODUCTION TO PROJECT FINANCE

1.1- Project finance
1.2- Non- recourse debt or mezzanine loan
1.3- Factors that needed to be considered to access

CHAPTER-2: METHODOLOGY
2.1- Aim of the Project.
2.2- Techniques for analysis.
2.3- Limitations of the Project.

CHAPTER-3: RESIDENTIAL/HOUSING SECTOR IN INDIA

3.1- Indian Market Size
3.2-Investments/Developments
3.3- Government initiatives for residential and housing

CHAPTER-4: DESCRIPTION OF THE PROJECT

4.1- Project details
4.2- Project location
4.3-Project size
4.4- Project Commencement & End Date
4.5- Project assumptions
4.6- Project revenue sources
4.7- Project Financing

CHAPTER-5: FINANCIAL ANALYSIS OF THE PROJECT

5.1- Cost Allocation
5.2- Revenue Growth
5.3- EBITDA
5.4- Debt Service Coverage Ratio
5.5- Final project cash flow
5.6- Project feasibility analysis

CHAPTRR-6: CONCLUSION

BIBLIOGRAPHY
LIST OF TABLES
Table 1: Project Details..............................................................................................................17
Table 2: Project Assumptions....................................................................................................17
Table 3: Project Revenue Sources..............................................................................................17
Table 4: Debt Schedule..............................................................................................................18
Table 5: Results..........................................................................................................................23
LIST OF FIGURES
Figure 1: CAPEX.......................................................................................................................20
Figure 2: OPEX..........................................................................................................................20
Figure 3: Revenue Growth.........................................................................................................21
Figure 4: EBITDA growth.........................................................................................................21
 1.1- PROJECT FINANCING-
Project finance is a long-term, no- or limited-recourse loan secured by the borrower's rights, assets,
and interests in the project.

To put it another way, "project financing" entails a corporate sponsor investing in and owning a
single-purpose, industrial asset through a legally separate corporation financed with non-recourse
debt.
The repayment of project finance is based on the revenues that the project will produce after it is
done, rather than the assets or balance sheet of the sponsoring firm. Project finance cannot be
classified as corporate finance since it cannot demonstrate that the revenue stream from the
completed project will be adequate to repay the loan.
Project financing incorporates-
• Long-term infrastructure, industrial projects, and public-sector services financing.
• A financial arrangement that is non-recourse or has limited recourse.
• Payment is made from the project's cash flow.

Methods of the Project Financing-

 Project funding can be done in three ways: 

 Cost share financing or low-interest loan financing.

 Debt financing is the second option.

 Financing using your own money.

The types of project for which project finance is suitable for are-

i. infrastructure projects, such as government buildings and transport systems;

ii. oil and gas exploration projects;
iii. sports stadia; and
iv. Liquefied natural gas development projects.
v. Mining
vi. Building
vii. Construction
viii. Real estate
 1.2- NON-RECOURSE LOAN AND MEZZANINE DEBT

Non-recourse debt is a loan that is not backed by any assets. Any consumer or business debt that is
secured only by collateral is referred to as a non-recourse loan. In the event of default, the lender is
prohibited from seizing any of the borrower's assets other than the collateral. Non-recourse loans
are frequently utilized to fund commercial real estate developments and other projects with a long
time to completion. In the case of real estate, the land serves as the loan's collateral. Non-recourse
debts include things like mortgages.
Mezzanine debt gets its name from the fact that it blurs the border between debt and equity. It is the
riskiest type of loan, but it also has some of the best returns—a typical rate is between 12 and 20
percent per year. In most buyouts, a mezzanine lender is brought in to showcase some of the cash
that would otherwise be committed by an equity investor. Mezzanine loans are a hybrid of
conventional and non-conventional financing.
1.3- FACTORS THAT NEED TO BE CONSIDERED TO ASSESS A NEW
VENTURE AS PROJECT FINANCE-
 Company principle quality—
Principles are the major sources of energy for business ventures. Their vision,
energy, and willingness to put out the work are the qualities that create or destroy
ventures.
 Threats to the business environment-
Lenders ensure that you are not seen as being at excessive risk. The impending
removal of a tariff barrier, a polluting practice, or the Because your company is in
a vulnerable area of the economy, a lender may be wary about lending to you. In
addition, the Company should have appropriate insurance coverage that is tailored
to the nature of its operations.
 .Project credibility-
Lenders and investors are willing to invest in your project because they believe it
will pay off. They'll make sure your proposal is reasonable and based on verified
facts.

 The firm's capacity to pay and financial structure-

You must demonstrate that the company can fulfill all of its financial obligations.
There should be a financial framework for the firm.

 The principal's financial history-

In the view of lenders, the past is a good predictor of the future. They will very certainly
perform a credit check on the business proprietors to discover if they have successfully met
previous financial obligations.

 Security-
Debt financing is often secured against a company's assets, which may be enough to cover
lenders' risk.

11
12
2.1- AIM OF THE PROJECT-
The aim of the project is to analyze the residential sector and housing scenario in Gurgoan, Haryana,
IN. Another requirement for preparing this report is to create a financial model for 50 flats housing
project and to know that the project is feasible or not.

2.2- ANALYSIS TECHNIQUES-

The techniques used for analysis are-

 Quantitative techniques
 Charts and graphical analysis

2.3- LIMITATIONS-

 The project debt obligation is very high as it is not able to fulfill.

 Revenue generated by the project is not enough to pay the debt.

 The project will not be able to generate the income that can cover the cost
itself.

 The project is totally non feasible.

 The location chosen for the project is also very expensive for
construction.
 3.1- INDIAN MARKET SIZE-

By 2040, real estate market will grow to Rs 65,000 crore (US$ 9.30 billion) from Rs 12,000 crore
(US$ 1.72 billion) in 2019. Real estate sector in India is expected to reach a market size of US$ 1
trillion by 2030 from US$ 120 billion in 2017 and contribute 13 per cent to the country’s GDP by
2025. Retail, hospitality, and commercial real estate are also growing significantly, providing the
much-needed infrastructure for India's growing needs. Indian real estate increased by 19.5 per cent
CAGR from 2017 to 2028.
Housing sales reached 2.61 lakh units in 2019 across seven major cities.

3.2-INVESTMENTS/DEVELOPMENTS-
The country's rapid urbanization is propelling real estate development. By 2020, urban regions
will have contributed more than 70% of India's GDP. The Indian government has been supportive
of the real estate industry. The Union Cabinet authorized 100 Smart City Projects in India in
August 2015. FDI (Foreign Direct Investment) restrictions for township and settlement
development projects have also been lifted to 100% by the government. Housing for All, a
government of India program, is anticipated to attract US$ 1.3 trillion in investment in the
housing industry by 2025. As of December 2019, 1.12 crore homes has been sanctioned in urban
areas under the Pradhan Mantri Awas Yojana (Urban) [PMAY (U)]. with a potential to create
1.20 crore jobs. The scheme is expected to push affordable housing and construction in the
country and give a boost to the real estate sector.

Some of the major investments and developments in this sector are as follows:
 As a sub-scheme under PMAY–U, the Union Cabinet authorized the building of Affordable Rental
Housing Complexes (AHRCs) for urban migrants and the destitute on July 9, 2020.
 As of December 2019, 1.12 crore homes had been sanctioned in urban regions under the Pradhan
Mantri Awas Yojana (Urban) [PMAY (U)], with the potential to create 1.20 crore employment.
 The government has also issued proposed rules for non-residential investment by Real Estate
Investment Trusts (REITs).
 In 2019, housing sales in seven main cities totaled 2.61 lakh units.

3.3-GOVERNMENT INITIATIVES FOR RESIDENTIAL AND HOUSING –

The government of India, in collaboration with the governments of the several states, has taken a
number of steps to promote growth in the industry. Real estate firms should take advantage of the
Smart City Project, which aims to construct 100 smart cities. Other important government projects
are listed below:

 The Union Cabinet has authorized the establishment of a Rs 25,000 crore (US$ 3.58 billion)
alternative investment fund to resuscitate approximately 1,600 delayed housing projects
throughout the nation (AIF).
 1.12 crore homes have been sanctioned in urban areas under the Pradhan Mantri Awas
Yojana (Urban) (PMAY (U)), producing 1.20 crore employment.
 The government has established a The National Housing Bank (NHB) established the
Affordable Housing Fund (AHF) with an initial corpus of Rs 10,000 crore (US$ 1.43
billion) to address the priority sector lending shortfall of banks and financial institutions for
HFC microfinance.
 4.1- PROJECT DETAILS-
Techvardhan Infra Pvt. Ltd (Any Company) “CLIENT” has acquired a piece of land near Gurugram
HR and wants to develop it as a residential building having 50 flats of 900 sq. ft each. They are
expecting to sell the flats at a rate of Rs. 4000 / sq.ft. The expected Capex is Rs. 8 Crore and Opex is
Rs. 50 Lacs / per annum for the whole project. They are seeking a non-recourse debt (project
financing) with 70:30 as D/E ratio from leading commercial banks in India as a 12 years term loan.

4.2- PROJECT LOCATION-

Gurgoan, formally gurugam, is a city in the northern Indian state of Haryana where the project
would be built. It's about 30 kilometers southwest of the national capital, New Delhi, on the
Delhi-Haryana border. In Gurgoan, there are architecturally significant structures in a variety of
styles and time periods.
With contemporary planning, the city has become home to a number of tall structures. A total of
1100 residential towers were planned at Gurgoan. A good condominium in Gurgoan costs at least
$160,130 for a 93 square meter (1,000 sq ft) two bedroom (10,000,000).
The factor to be considered while selecting the building location is as follows:-

 There is access to a park and a playground.

 Public utility services, including water, electricity, and sewage disposal, are readily available.

 Land contour in relation to construction costs. The price of land.

 Workplaces are a long way away.

 Drainage is simple.

 In terms of school, college, and public buildings, the location is ideal.

 Transportation services are available.

 Other necessary amenities

4.3- PROJECT SIZE-

The project is going to be developed in 5000 sq. ft. where each flat would be 900 sq. ft. and others
details related to the construction of project is a follows-

 There would be 50 flats.

 It would have ten stories with five 900 sq. ft. units on each floor. The structure will have a suitable number of
lifts, which will be 4-5.

 For the residential covering, there would be a parking lot.

 The structure would feature the most up-to-date design and infrastructure.
The table below shows the necessary project details-
Table 1: Project Details-

PROJECT DETAILS
Size in Sq. Ft 45000 80.01
Equity 30% 24.003
Debt 70% 56.007
Debt Servise Reserve (DSR) 1 yr  

4.4- PROJECT COMMENMENT & END DATE-

The project is going to start 1-may -2021

The end date of project will be 30-Nov-2033

4.5- PROJECT ASSUMPTIONS-

Table 2: Project Assumptions

ASSUMPTIONS
Inflation 4.00% Debt rate 10.00% USD/INR 75
DDT 0.00% Moratorium 1 yr Discount 10.00%
Tax Holiday 0 yr Debt Tenure 4 yrs Construction Time 1 yr
Tax Rate 25.00% Depreciation 7.00% MAT 15.00%

4.6- PROJECT REVENUE SOURCES-

Table 3: Project Revenue Sources-

Revenue Parameters  
City Gurgaon, Haryana
Size of one flat (Sq. Ft.) 900
Total size (Sq. Ft) of 50 flats 45000
Rate per Sq. Ft. (INR) 4000
SaleS price per flat (INR) 3600000
Avg. no. of flats sold per year 5
Sales Price Appreciation 10%
Interest on Deposit 8%
4.7- PROJECT FINANCING-
Table 4: Debt Schedule

Debt Amount 56.007

Debt Rate 10%
Moratorium 1 yr
Term 12 yrs
Payment Periods 10
One period is One year
COD 10-Jul-21
First year end 09-Jul-22

The project is being financed through a commercial bank through debt and equity in 70:30. The
schedule of debt repayment is for 12 years in 48 period (one period means one quarter) respectively.
5.1- COST ALLOCATION-
The total project cost (CAPEX) of the project Rs. 80,000,000 and the total of operating expenses
(OPEX) is Rs. 5,000,000 per annum. The expenses are expected to rise as per the inflation rate
that is approx. 4%. The allocation of the total project cost is as follows-

Project Cost

Land
Construction Cost
Interior Decoration
3% Fixtures
1% 3% 1% 0%
3% Furniture
1% 7% Building Registration
45%
7%
0% Broker Fee
3% Stamp Duty
Fund Raising Fee
25% Transfer of Deed Fee

Interest during Moratorium

Loan and Documentation Fee

HSE

Figure 1: CAPEX

Property Maintenance
Salary (Sweeper +
Accountant)
Utilities (Electric, Water
etc)
Property Insurance
Property Tax
Marketing Expenses
Plumber +Electrician +
Misc. etc.

Figure 2: OPEX

20
5.2- REVENUE GROWTH-
The revenue growth during the period of 10 years has been increasing constantly due to the
appreciation in the value of flat that are been projected as follows-

Total Revenue
Revenue (million INR)
50
45 43.57
39.61
40 36.01
35 32.74
29.76 Revenue (million INR)
30 27.06
24.60 Linear (Revenue (million INR))
25 22.36
20.33
20 18.48
15
10
5
0
1 2 3 4 5 6 7 8 9 10

Figure 3: Revenue Growth

5.3- EBITDA GROWTH -

The EBITDA has been constantly growing negatively during the period of 10 years. The continuous
EBITDA is shown as follows-

EBITDA
EBITDA
40.000
35.000 36.493
32.804
30.000 29.464
25.000 26.442
23.708 EBITDA
20.000 21.235
Axis Title 18.978
16.958
15.000 13.485 15.133
10.000
5.000
0.000
1 2 3 4 5 6 7 8 9 10
Fig
ure 4: EBITDA growth
21
5.6- PROJECT FEASIBILITY ANALYSIS-
The best way to find out whether your project is feasible is to complete a Feasibility Study. This
process helps you gain confidence that the solution you need to build can be implemented on time
and under budget.
The importance of a feasibility study is to establish whether or not a company, team, or
organization will deliver on its promises in a satisfactory manner and a reasonable period of time.
These are the five questions most feasibility studies have to answer in order to justify a new
project, plan, or method:
 Is this plan technically feasible?

 Is this plan legal?

 Is this plan operationally feasible?

 Is this plan feasible within a reasonable

period of time?

 Is this plan economically feasible?

You can assess this area of feasibility based on several different factors, including:

 Projected profitability
 The total cost of completion
 Estimated investment by outside parties

No matter how incredible a project may seem, if the numbers don’t add up, then either you’ll have to
seek out larger budgets or the plan isn’t worth the risk.

Table 5: Results

Results
Equity IRR 20.66%
Min DSCR 1.302
Avg DSCR 2.056
Project IRR 13.29%

The following result shows the internal rate of return of the project which is 13.29% and equity
internal rate of return is 20.66%. The average DSCR from the project is 2.056. DSCR is an important
measure to consider the feasibility of the project. DSCR interpretation is important to the debt
obligation and repayment capacity of the project.
DSCR is interpreted as:

DSCR<1:

You have negative cash flow. You don’t have enough income to service all of the debt.

DSCR >1:

You have positive cash flow. The higher you’re DSCR, the more income you have to pay off your debt.

DSCR=1:

You have exactly enough cash coming in to service your debt, but you don’t have additional cash flow other than that.

So, from the following it could be clearly interpreted that the project is not feasible for being build and constructed and
to fulfill its debt obligation and to earn income from it. The following time period is also not suitable for scheduling the
project.
 CONCLUSION

From the following report and analysis it can be conclude that there is difference between the
theoretical and practical work done. As the scope of understanding will be much more when practical
work is done. As, we get more knowledge in such a situation where we have great experience doing
the practical work.
On the basis of financial model being provide in the module 2 of the project I am being able to
understanding the concept of financial modeling.

The overall internship sessions are divided into four modules that are as
-
Module1- It is basically about the Project Finance and the difference between project finance and
corporate finance. It helped to know the different terms related to project finance.

Module 2- It helps to know the assumptions that need to be kept in mind while preparing a financial
model and function of revenue, cost and debt sheet in financial model and the steps involved in
preparation of fin flow sheet.

Module 3- This task helped to know that how a venture is assessed to qualify as project finance and
how revenue models are prepared for different types of projects either they are residential, solar PV
or PPP projects.

Module 4- This is the last module of the internship and the main module. A project report and
financial model is being prepared in the project. The project report gives the brief explanation of
what the Project finance, what is the Indian scenario in residential and housing sector, how to prepare
a financial model and how to know the feasibility of the project and analyzing the project.

From the analysis it has been cleared that the Project on Financial Modeling and Analysis of 50 Flats
Housing project in Gurgoan, Haryana is not feasible and is not good to be considered as a feasible.
As, the debt obligations of the project are so high and the income generation is low at the given point
of time. So, the project is not viable to be executed.

The Overall experience during the internship was very good and fruitful as it helped to learn many
new things, including financial modeling and analyzing a project.
 BIBLIOGRAPHY

1. https://en.wikipedia.org/wiki/Gurgaon

2. https://www.ibef.org/uploads/industry/Real-Estate-June_2020.jpg

3. https://techvardhan.com/

4. https://www.ibef.org/industry/real-estate-india.aspx

5. https://mubrealestate.com/india/learn-the-truth-about-real-estate-industry/