Mortgage Formulas

Mortgage Formulas is a Coda Pack for mortgage loan originators and real estate investors. It bundles a layered domain library (mortgages, deals, properties, investors, analysis) with a wide pack surface: formatting helpers, creation factories, qualification and risk analysis, payment and amortization tooling, refinance comparisons, property projections, and sync tables for amortization schedules and investor deals.

Overview

• Purpose: Model mortgages, deals, and properties end to end inside Coda -- from raw inputs through amortization, qualification ratios, risk scoring, refinance analysis, and investor deal records.
• Inputs: Numeric loan parameters (amount, rate, term, program), borrower financial data (income, debts, credit score, down payment), property data (price, taxes, insurance), and JSON payloads that flow between creation formulas.
• Outputs: Currency- and percent-formatted strings, structured analysis objects (qualification, risk, refinance, ARM, HELOC), amortization rows, and deal records.
• Audience: Loan originators, real estate investors, and Coda builders constructing mortgage analysis docs and investment deal trackers.

Requirements

• Account(s): Coda account with permission to install packs.
• Credentials: None.
• External setup: Not applicable. All calculations run locally; no third-party API is called.
• Limits: Domain calculations validate inputs through ValidationErrors in the library layer (e.g. positive amounts, in-range LTV) and surface them as user-visible errors. Programs and occupancy types must match the values defined in the underlying library.

Installation

1. Install the pack in a Coda doc.
2. No authentication is required.
3. Add the AmortizationSchedules and/or Deals sync tables and supply the sync parameters described under Tables.
4. Use the formulas (e.g. CreateProperty, CreateInvestor, CreateMortgage, CreateDeal, DetailedAmortization, RefinanceAnalysis, RiskAssessment) directly in formulas, columns, or buttons.
5. Optionally apply the bundled column formats (Currency, Percentage, Rate, MonthlyPayment, LoanAmount) to existing numeric columns.

Authentication

This pack does not use external authentication.

• Method: None.
• Required scopes or permissions: Not applicable.
• Where credentials are entered in Coda: Not applicable.

Formulas

Name	Type	Description	Inputs	Returns
FormatCurrency	Formula	Formats a number as currency with optional decimals.	amount, decimals?	Text
FormatPercentage	Formula	Formats a decimal as a percentage.	value, decimals?	Text
FormatRate	Formula	Formats an interest rate decimal with precision.	rate	Text
FormatMonthlyPayment	Formula	Formats a monthly payment as currency.	payment	Text
FormatLoanAmount	Formula	Formats a loan amount as currency without cents.	amount	Text
CreateProperty	Formula	Builds a Property domain object from raw fields.	id, street, city, state, zip, askingPrice, sqft, annualTax, annualInsurance, monthlyRent	Property object
CreateInvestor	Formula	Builds an Investor domain object.	name, cash, annualIncome, taxRate, fixedExpense, variableExpense	Investor object
CreateMortgage	Formula	Builds a Mortgage domain object.	loanAmount, interestRate, program	Mortgage object
CreateDeal	Formula	Builds a Deal from investor + property + collateral JSON and loan terms.	investorJson, purchasePropertyJson, collateralPropertyJson, ltv, defaultInterestRate, program, managementFeePct, closingCost, occupancy	Deal object
AnalyzeCredit	Formula	Returns a credit profile classification with contributing factors.	score	Object
RequiredReserves	Formula	Computes required reserves (months) for a given occupancy and loan amount.	occupancy, loanAmount	Number
MaxLoanAmount	Formula	Estimates a maximum loan amount from income, debts, credit score, and down payment.	monthlyIncome, monthlyDebts, creditScore, downPayment	Number
BreakEvenTimeline	Formula	Generates a break-even timeline for closing costs vs monthly savings.	closingCosts, monthlySavings, months	Array
TimeValue	Formula	Computes a time-value surrogate for a monthly benefit stream.	months, monthlyBenefit	Number
PaymentSchedule	Formula	Compares payment frequencies (monthly, bi-weekly, etc.) and their impact.	loanAmount, interestRate, mortgageProgram	Object
ExtraPaymentAnalysis	Formula	Analyzes the impact of recurring extra principal payments.	loanAmount, interestRate, mortgageProgram, extraMonthly	Object
AdvancedPaymentAnalysis	Formula	Compares multiple payment strategies and their impact over time.	loanAmount, interestRate, term, …	Object
DetailedAmortization	Formula	Generates a detailed amortization analysis with optional yearly milestones.	loanAmount, interestRate, mortgageProgram, yearlyMilestones	Object
InterestAnalysis	Formula	Comprehensive interest cost breakdown for a mortgage.	loanAmount, interestRate, mortgageProgram	Object
ARMScenarioAnalysis	Formula	Models ARM scenarios across initial rate, type, margin, and index.	loanAmount, initialRate, armType, margin, indexRate	Object
HELOCAnalysis	Formula	Analyzes HELOC scenarios, draw period, and payments.	propertyValue, existingMortgageBalance, requestedLineAmount, helocRate, drawPeriodYears	Object
CalculateRatios	Formula	Calculates the key lending ratios (LTV, DTI, front/back, etc.).	Loan and borrower numerics	Object
QualificationAnalysis	Formula	Comprehensive borrower qualification analysis.	Loan and borrower numerics	Object
RiskAssessment	Formula	Detailed risk assessment with stress testing across the deal.	Loan, borrower, property numerics	Object
RefinanceAnalysis	Formula	Comprehensive refinance analysis across multiple scenarios.	Existing and proposed loan terms, costs	Object
RefinanceScenarioComparison	Formula	Side-by-side comparison of refinance scenarios.	Scenario inputs	Array
ClosingCostAnalysis	Formula	Detailed analysis of refinance closing costs and fees.	Closing cost inputs	Object
PropertyValueProjections	Formula	Projects property value across years using appreciation and volatility assumptions.	currentValue, annualAppreciation, yearsToProject, marketVolatility	Array
CalculateInsuranceAndTaxes	Formula	Estimates annual insurance and property taxes.	Property numerics	Object

Tables

• AmortizationSchedules -- produces a per-period amortization schedule from a loan amount, interest rate, and mortgage program. Sync parameters are required (loan amount, rate, program). The pack rebuilds the schedule from the library each sync; rows are keyed by scheduleId-period.
• Deals -- investment deal analysis records constructed from a JSON array of DealInput objects supplied as the sync parameter dealDataJson. Each row captures investor, property, mortgage, payments, occupancy, LTV, and APR derived through the domain layer.

Both tables are full-refresh; pass updated parameters and re-sync to regenerate output.

Columns

This pack registers five column formats backed by the formatting formulas. Apply them to a numeric column and the column value is rendered using the corresponding helper.

• Currency -- renders a number via FormatCurrency.
• Percentage -- renders a number via FormatPercentage.
• Rate -- renders an interest-rate decimal via FormatRate.
• MonthlyPayment -- renders a payment value via FormatMonthlyPayment.
• LoanAmount -- renders a loan amount via FormatLoanAmount.

Example usage

Build an investor deal end to end

1. Call CreateInvestor("Alice", 150000, 220000, 0.32, 4500, 800) and store the JSON result in a column.
2. Call CreateProperty(...) for the purchase property and (optionally) a separate collateral property; store both as JSON.
3. Call CreateDeal(investorJson, purchasePropertyJson, collateralPropertyJson, 0.8, 0.065, "Conventional30", 0.08, 7500, "PRIMARY") to assemble the deal.
4. Add the Deals sync table with dealDataJson set to a JSON array of deal inputs to surface the deals as rows.
5. Apply the Currency and Percentage column formats to numeric columns to keep the output consistently formatted, and run RiskAssessment or RefinanceAnalysis on the same inputs to layer in scenario analysis.

Limitations

• The pack does not call any external rate, tax, or property data service; supply all market data yourself.
• Supported mortgage programs and occupancy types are limited to the values defined in src/lib/mortgages and src/lib/common/types. Unknown values surface as ValidationErrors.
• RiskAssessment, RefinanceAnalysis, and similar analyses produce model output suitable for analysis and education, not for legal, regulatory, or production lending decisions.
• JSON inputs to CreateDeal and the Deals sync table must match the shape produced by the corresponding Create* formulas; arbitrary objects will fail validation.
• The bundled Deals sync table generates dealIds with Date.now(), so re-syncs produce new IDs and rows are not stable across runs.

Troubleshooting

Problem	Likely cause	Resolution
Invalid JSON input for investor or property	A JSON parameter passed to CreateDeal (or another wrapper) is not parseable	Verify the JSON string with ToJson() or by inspecting the source value
Deal data must be a JSON array	dealDataJson for the Deals sync table is not a JSON array	Wrap your deal inputs in [ ... ] before passing them in
Failed to parse deal data: ...	The Deals sync parameter could not be parsed at all	Check JSON quoting and escape characters in the formula input
Deal N: <ValidationError message>	The Nth deal failed library validation (negative amount, bad LTV, unknown program, etc.)	Fix the offending field on that specific deal input
Invalid investor input / Invalid property input / Invalid deal input	A required field was missing or out of range during creation	Verify all required fields and that numerics are positive and within documented ranges
Sync output empty or unchanged	Sync parameters were not supplied or the same parameters were resubmitted	Provide the required sync parameters (loan amount, rate, program for amortization; deal JSON for deals) and re-sync
Formatted columns show raw numbers	Column format not applied	Set the column format to Currency, Percentage, Rate, MonthlyPayment, or LoanAmount

Development notes

• Entry point: src/pack/pack.ts (root pack.ts is a shim).
• Architecture: pure domain layer in src/lib/ (no Coda SDK dependency) with a thin pack layer in src/pack/ that wires the domain into formulas, sync tables, and column formats. See docs/ARCHITECTURE.md.
• Build: pnpm run build (runs build:lib and build:pack project references).
• Validate: pnpm run coda:validate
• Lint: pnpm run lint
• Typecheck: pnpm run typecheck
• Test: pnpm run test (mocha)
• Watch: pnpm run watch (parallel build:watch for the lib and pack layers)
• Docs: pnpm run generate:docs regenerates Markdown API docs into docs/ via TypeDoc (typedoc.json, tsconfig.typedoc.json).
• Pre-commit: managed by Husky (.husky/pre-commit), set up by the prepare script.
• Upload: pnpm run coda:upload

Repository

• Source: https://github.com/ramiisaac/coda-pack-mortgage-formulas
• Issue tracking: https://github.com/ramiisaac/coda-pack-mortgage-formulas/issues

License

MIT. Copyright (c) 2026 Rami Isaac.

Author

Author: Rami Isaac https://github.com/ramiisaac