VARDHAN CONSULTING ENGINEERS

Financial Modelling and

Analysis of 50 Flats
Housing Project in
Gurgaon, Haryana IN
Project Finance Modeling and Analysis
Atul kumar

1
 ACKNOWLEDGEMENT

The report was written as part of the VCE-Internships module assignment. It is based
on the analysis and financial modelling 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 offering a
platform for learning about project finance and modelling as well as for doing a virtual
internship.

This has provided me with the opportunity to learn about project finance or project
analysis, as well as an understanding of how to develop a financial model.
Finally, I’d like to thank my mentor, Mr. Ashish Kumar, CEO of Vardhan Consulting
Engineers, for offering guidance throughout this project via live interaction sessions.

I’d like to express my gratitude to Miss Neha Kumari for giving me the opportunity to
intern at Vardhan Consulting Engineers.

It’s a joy to be a part of the Vardhan Consulting Engineers internship programme.

During the assignment, I gained a lot of information and experience.

-Atul Kumar

ABSTRACT

Gurgoan (formally gurugam) is a city in Haryana, India's northernmost state. It's about 30
kilometres southwest of the national capital, New Delhi, near the Delhi-Haryana border. In
Gurgoan, there are architecturally significant buildings in a variety of styles and time
periods. With modern planning, the city has been home to a number of tall skyscrapers. A
total of 1100 residential towers were planned at Gurgoan. A nice condominium in Gurgoan
costs at least $160,130 for a 93 square metre (1,000 sq ft) two bedroom (10,000,00

2
 EXECUTIVE SUMMARY

VCE is a consulting firm created by a group of engineers with excellent academic backgrounds
and decades of managerial expertise gained through working in a variety of firms around the
world. Clients' complicated engineering, managerial, and financial concerns are addressed by
VCE.

Energy projects benefit from our engineering 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 lead consultants have 10+ years of experience in energy sector in India, Philippines, UK,

Cambodia and Thailand. We are specialist in Solar PV projects, we provide tailored made
engineering and management solutions for the client’s needs VCE have different business
horizons and revenue sources such as;

 Engineering and Management

 Consulting. Importing and
 Branding Pearl Jewellery. Stock
 Market and Cross-Currency
 Trading. Insurance and Investment
 Advisory.

From past 3 years we saw a good growth in our all business and expecting good returns to all

3
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

We pick students from various engineering and management universities for this
programme and provide them with internships and training. Our internships and
training are one-of-a-kind, and they are designed specifically for students in the Core
Engineering Sector (Electrical, Mechanical, Civil, and Energy Engineering), as well as
Finance Management, to help them become corporate / industry ready. We provide
them with industry mentorship. All of this is done without charging or favouring the
pupils in any way. For this, we currently provide Rs. 60,000 (Sixty Thousand Rupees)
per year. As an incentive, the majority of the money will be paid as a stipend to the best
interns. (Total stipend paid: 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.

I learned a lot of new things and got a lot of new abilities while doing this internship with
VCE. This internship's last module is quite beneficial in learning how to conduct research
and develop a financial model. In the subject of finance, this is a very useful instrument.

This research will help you understand the structure of India's residential and housing
industries, the opportunities for investment in residential projects, programmes and laws,
and an overall analysis of residential growth and housing in Gurgoan, Haryana, IN..

4
 TABLE OF CONTENTS

CHAPTER-1: PROJECT FINANCE

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

CHAPTER-2: METHODOLOGY & EXPLANATION

2.1- Project’s Aim.
2.2- Techniques for analysis.
2.3- Limitations of the Project.

CHAPTER-3: HOUSING SECTOR OF 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- Projectdetails
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

5
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
Figure 5: DSCR..........................................................................................................................22
Figure 6: Final Project Cash flow..............................................................................................22

6
7
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.

"Project finance" is defined as "a corporate sponsor investing in and owing a single-purpose,
industrial asset through a legally separate business financed with non-recourse debt."

The repayment of project finance is based on the revenues that the project will generate once it
is done, rather than the assets or balance sheet of the sponsoring company. Corporate finance is
unable to show that the revenue stream from the completed project would be sufficient to repay
the loan, which is why Project finance under corporate finance.

Project financing incorporates-

 Financing of long-term infrastructure, industrial projects, and public services.

 Non-recourse/limited recourse financial structure.
 Payment from cash flow generated by the project.

Project Financing Methods-

There are three methods in Project Financing-
 Cost Share Financing or Low interest loan financing.
 Debts Financing.
 Equity Financing.

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

8
1.2- NON-RECOURSE LOAN AND MEZZANINE DEBT

Non-Recourse debt is a loan that is not backed by any assets. Any consumer or commercial
debt that is secured solely 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. Commercial real estate initiatives and other projects with a significant lead time to
completion are frequently financed with non-recourse loans. In the case of real estate, the land
serves as the loan's collateral. Non-recourse debts include things like mortgages.

Mezzanine debt Its name comes 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 capital that would otherwise be invested by an equity investor. Mezzanine loans
are a combination of debt and equity financing..

1.3- FACTORS THAT NEED TO BE CONSIDERED TO ASSESS A NEW

VENTURE AS PROJECT FINANCE-

1. Company principle quality—Principles are the fundamental sources of energy for

business ventures. Their vision, energy, and willingness to put forth the effort are the
components that make or break projects.

2. Risks in the business environment- Lenders ensure that you are not seen to be at
excessive risk. A lender may be overly cautious due to the impending removal of a tariff
barrier, a polluting procedure, or the fact that your business is located in a vulnerable area
of the economy. In addition, the Company should have enough insurance coverage that is
tailored to the nature of its operations.

3. Project credibility-
If lenders or investors decide to put money in your project, it’s because they hope the
investment will pay off. They’ll make sure your provision are based on verifiable facts
are realistic.

4. Company’s ability to pay and financial structure-

You’ll have to prove that the company is able to meet all its financial obligation. The
company’s financial structure should therefore show a healthy balance between loans
and assets.

5. Principal’s financial history-

In lenders eyes, the future is largely be predicted by past. It is more than likely that
they will run a credit check on the business principals to see if principles effectively
met past financial obligation.

6. Security-
Debt financing is usually secured against company assets, which could be sufficient to
allow lenders to cover their risk.

10
11
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.

12
13
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 urbanisation 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 authorised 100 Smart City Projects in India in
August 2015. FDI (Foreign Direct Investment) limitations for township and settlement
development projects have also been lifted to 100% by the government. Housing for All, a
government of India project, is estimated to attract US$ 1.3 trillion in investment in the housing
sector by 2025.

Some of the major investments and developments in this sector are as follows:
 On July 09, 2020, Union Cabinet approved the development of Affordable Rental
Housing Complexes (AHRCs) for urban migrants and poor as a sub-scheme under PMAY–
U.
 As of December 2019, under Pradhan Mantri Awas Yojana (Urban) [PMAY (U)], 1.12
crore houses were sanctioned in urban areas, with a potential to create 1.20 crore jobs.
 Government has also released draft guidelines for investment by Real Estate Investment
Trusts (REITs) in non-residential segment.
 Housing sales reached 2.61 lakh units in 2019 across seven major cities.

3.3-GOVERNMENT INITIATIVES FOR RESIDENTIAL AND HOUSING –

Government of India along with the governments of respective States has taken several
initiatives to encourage development in the sector. The Smart City Project, with a plan to
build 100 smart cities, is a prime opportunity for real estate companies. Below are other
major Government initiatives:

 In order to revive around 1,600 stalled housing projects across top cities in the
country, the Union Cabinet has approved the setting up of Rs 25,000 crore (US$
3.58 billion) alternative investment fund (AIF).
 Under Pradhan Mantri Awas Yojana (Urban) (PMAY (U)), 1.12 crore houses have
been sanctioned in urban areas, creating 1.20 crore jobs.
 Government has created an Affordable Housing Fund (AHF) in the National
Housing Bank (NHB) with an initial corpus of Rs 10,000 crore (US$ 1.43 billion)
using priority sector lending short fall of banks/financial institutions for micro financing.
14
15
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-

The Project going to be constructed in Gurgoan, officially gurugam, is a city located in the
northern Indian state of Haryana. It is situated near the Delhi-Haryana border, about 30
kilometers southwest of the national capital New Delhi. Gurgoan has architecturally
noteworthy buildings in a wide range of styles and distinct time periods.

The city has been home to several tall buildings with the modern planning. Gurgoan had
estimated 1100 residential rises. The average cost of a 93 square meter (1,000 sq ft) two
bedroom at a decent condominium in gurgoan is at least $160,130 (10,000,000).

The factor to be considered while selecting the building location is as follows:-

 Access to park & play ground.

 Availability of public utility services, especially water, electricity & sewage
disposal.
 Contour of land in relation the building cost. Cost of land.
 Distance from places of work.
 Ease of drainage
 Location with respect to school, collage & public buildings.
 Transport facilities.
 Other essential facilities.

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-

 It would have 50 Flats.

 It would have 10 floors and each floor would have 5 flats of 900 sq. ft. each. The
building would have sufficient no. of lifts that will be 4-5.
 There would a parking area for the residential coverage.
 The building would have the best trending and best infrastructure facilities.

16
The table below shows the necessary project details-
Table 1: Project Details-

PROJECT
DETAILS
Size in sq. ft 5,000.00
Size Per Flat (Sq. ft.) 900.00
No. of Flats 50.00
Equity 30%
Debt 70%
Debt service reserve 0.10 yrs

4.4- PROJECT COMMENMENT & END DATE-

 The project was started on 4-Oct-2020

 The end date of project will be 30-Nov-2032

4.5- PROJECT ASSUMPTIONS-

Table 2: Project Assumptions

ASSUMPTIONS
Inflation 7.66% Construction Time 0.10 yrs
Tax rate 12% MAT 15%
Debt Rate 10% Discount 10%
Moratorium 0.10 yrs USD/INR 73.16
Debt tenure 12.00 yrs DDT 0
Depreciation 5.00% Flat Appreciation 10.00%

4.6- PROJECT REVENUE SOURCES-

Table 3: Project Revenue Sources-

Revenue Parameters
City GURUGAM, HARYANA
Size (Sq. ft) 5,000.00
Size Per flat (Sq. ft) 900.00
No of Floors 10.00
No. of Flats 50.00
Selling Price (Sq. ft) 4,000.00
Selling Price Per Flat 3,600,000.00
Flat Appreciation 10%
Sales commission 2%

17
4.7- PROJECT FINANCING-
Table 4: Debt Schedule

Details
Debt Amount 56.00
Debt rate 10.00%
Moratorium 0.10 yrs
Term 12.0 yrs
Payment Periods 48
One period is one quarter
COD 6-Oct-2020
First Quarter End 4-Jan-2021

18
19
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. 7.2%. The allocation of the total project cost is as follows-

Loan and Total Project Cost

Documentation Fee
Tranfer of 2% Interest During Flat Construction
Deed Fee Moratorium CSR, HSE, Cost
Training
0% 1% 14% 18%

Fund Raising Stamp Duty

Fee
0% 4% Interior Decoration
Broker Fee 20%
3% Furniture
Building Fixtures 21%
Registration 13%
1% Lift
4%

Figure 1: CAPEX

Operating Expenses
Building Maintain nce
a
5%
20%
Utilities (Electric + Water
+ Internet)
57% 10 Salary (Maid + Acountant)
%

8%
Plumber + Electrician + Misc etc

Insurance

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
45.000
40.000
35.000
30.000
25.000
20.000
15.000
10.000
5.000
0.000
1 2 3 4 5 6 7 8 9 10
Total Revenue 17.88 19.66 21.63 23.79 26.17 28.79 31.67 34.84 38.32 42.16

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
0.000
-10.000
-20.000
-30.000
-40.000
-50.000
-60.000
-70.000
-80.000
1 2 3 4 5 6 7 8 9 10
Series1 -42.1 -44.9 -47.9 -51.0 -54.4 -57.9 -61.7 -65.7 -69.9 -74.4

Figure 4: EBITDA growth

21
5.4- DEBT SERVICE COVERAGE RATIO-
DSCR is measure of thee cash flow to available to pay current debt obligation. The
average DSCR of the company is 1.25.

DSCR
25.000
20.000
15.000
10.000
5.000
0.000
-5.000
-10.000
-15.000
1

2 3 4 5 6 7 8 9 10 11 12
DSCR -1. -2. -2. -4. -13 20. 5.8 3.4 2.4 1.9 7.6 -1.

Figure 5: DSCR

5.5- FINAL PROJECT CASHFLOW-

The final project cash flow of the company during the construction and debt repayment
schedule is as follows-

Final Project Cashflow

10.000
8.000
6.000
4.000
2.000
0.000
-2.000
-4.000
-6.000
1 2 3 4 5 6 7 8 9 10 11 12

Final Project Cashflow 8.0

2 2 1 1 0 - - - - - -

Figure 6: Final Project Cash flow

22
5.6- PROJECT FEASIBILITY ANALYSIS-
A Feasibility Study is the best technique to determine whether your proposal is feasible. This
method gives you assurance that the solution you're working on will be delivered on time and on
budget.
A feasibility study's purpose is to determine whether a firm, team, or organisation will be able to
deliver on its claims in a satisfactory manner and within a fair time frame.

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 0.00%
Min DSCR -131.83
Avg. DSCR 12.46
Project IRR -23.32%

The following result shows the internal rate of return of the project which is -23.32% and
equity internal rate of return is 0.00%. The average DSCR from the project is 12.46. 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.

23
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.

24
LEARNINGS

25
 Learnings

It can be concluded from the following report and analysis that there is a difference between the
theoretical and actual work done. Because when practical labour is done, the field of
understanding will be much broader. As we gain more information in such a setting when we have
a lot of practical experience.
I am able to understand the notion of financial modelling based on the financial model provided in
module 2 of the project.

The internship sessions are broken down into four modules, which are as follows:

Module 1 is primarily concerned with project finance and the distinction between project finance
and corporate finance. It helped to know the different terms related to project finance.

Module 2- It explains the assumptions that must be considered when creating a financial
model, as well as the functions of the revenue, cost, and debt sheets in a financial model, as
well as the methods involved in creating a financial flow sheet.

Module 3- This work taught me how to examine a venture to see if it qualifies for project
finance and how to create revenue models for various sorts of projects, such as residential,
solar PV, and PPP.

Module 4- This is the internship's final and most important module. The project is now
preparing a project report and financial model. 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.

26
 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/

6. India Real Estate Market Outlook 2019 | CBRE

7. Top 10 Property Developers in India: A Complete Guide (asiapropertyhq.com)

27