Financing Costs Analyzer

Overview

This project implements a analyzer to extract and summarize financing costs from Excel applications, as requested in the internship technical exercise. The goal is to benchmark financing costs across projects by analyzing the last funding round.

Technical Stack

• Python 3.13+
• pandas
• numpy

Approach

1. File Processing

   • Traverse all Excel files in assets/input.

2. Parsing

   • Extract line items and totals from Sources & Uses Budget tab.
   • Validate totals and flag inconsistencies.
   • Extract total units and total square footage from Application tab.
   • Extract total new construction costs for derived metrics.

3. Computation

   • Calculate combined financing costs, per unit, per SF, and percentage of hard costs.
   • Track any missing or fallback values.

4. Output Generation

   • Individual TXT summaries per application (output/summaries/)
   • Combined summary TXT (output/summary.txt) and Excel (output/summary.xlsx)
   • Highlight flagged applications with discrepancies.

How to Run?

1. Install dependencies: pip install -r requirements.txt
2. Place Excel files in assets/input/
3. Run the script: python main.py
4. Check results in the output/ folder.

Output

• Individual summaries: output/summaries/xxx_summary.xlsx
• Combined Excel summary: output/summary.xlsx
• Combined text summary: output/summary.txt

Example Output

Take 25-673.xlsx and 25-674.xlsx as examples!

============================================================
Application: 25-673
Flagged: False
------------------------------------------------------------
Construction Interest & Fees
------------------------------------------------------------
Construction Loan Interest          $   5,652,628
Origination Fee                     $     421,959
Credit Enhancement/Application Fee  $           0
Bond Premium                        $     553,968
Cost of Issuance                    $     673,891
Title & Recording                   $     136,680
Taxes                               $     118,594
Insurance                           $   1,083,004
Other: LAHD ULA + HHH Interest      $     741,971
------------------------------------------------------------
Reported Total: $9,382,695
Calculated Total: $9,382,695
Reconciliation Validation: PASS
Missing Items: N/A
------------------------------------------------------------
Permanent Financing
------------------------------------------------------------
Loan Origination Fee                $      60,113
Credit Enhancement/Application Fee  $           0
Title & Recording                   $      20,000
Taxes                               $           0
Insurance                           $           0
Other: Legal Expenses               $      20,000
------------------------------------------------------------
Reported Total: $100,113
Calculated Total: $100,113
Reconciliation Validation: PASS
Missing Items: N/A
------------------------------------------------------------
Combined Financing Costs: $9,482,808
Per Unit: $86,998.24
Per SF: $106.88
Financing Costs as a Percentage of Hard Costs: 17.26%
Hard Costs Availability: YES
============================================================

============================================================
Application: 25-674
Flagged: True
------------------------------------------------------------
Construction Interest & Fees
------------------------------------------------------------
Construction Loan Interest          $   4,716,225
Origination Fee                     $     411,000
Credit Enhancement/Application Fee  $           0
Bond Premium                        $           0
Cost of Issuance                    $     213,262
Title & Recording                   $      60,000
Taxes                               $      80,000
Insurance                           $     450,000
Const. Lender Exp. & Const. Insp. Fees $      40,759
------------------------------------------------------------
Reported Total: $5,971,246
Calculated Total: $5,971,246
Reconciliation Validation: PASS
Missing Items: N/A
------------------------------------------------------------
Permanent Financing
------------------------------------------------------------
Loan Origination Fee                $     112,111
Credit Enhancement/Application Fee  $           0
Title & Recording                   $      30,000
Taxes                               $           0
Insurance                           $           0
Perm Lender Fees                    $      10,000
------------------------------------------------------------
Reported Total: $152,111
Calculated Total: $152,111
Reconciliation Validation: PASS
Missing Items: N/A
------------------------------------------------------------
Combined Financing Costs: $6,123,357
Per Unit: $73,775.39
Per SF: $89.85
Financing Costs as a Percentage of Hard Costs: N/A
Hard Costs Availability: NO
============================================================