excel_create_loan_analysis
Generate a loan amortization worksheet with payment schedules and accounting entries for financial analysis and reporting.
Instructions
Create a loan amortization analysis worksheet with payment schedule and accounting entries
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| annualRate | Yes | Annual interest rate (e.g., 0.05 for 5%) | |
| loanAmount | Yes | Principal loan amount | |
| worksheetName | No | Loan Analysis | |
| years | Yes | Loan term in years |
Implementation Reference
- src/tools/excel-tools.ts:651-676 (handler)The main handler function for the 'excel_create_loan_analysis' tool. It calls ProfessionalTemplates.createLoanAnalysisWorksheet to generate data, adds a worksheet, writes the data, auto-fits columns, and returns success/error.handler: async (args: any): Promise<ToolResult> => { try { const worksheetData = ProfessionalTemplates.createLoanAnalysisWorksheet(args.loanAmount, args.annualRate, args.years); const worksheetName = args.worksheetName || "Loan Analysis"; await excelManager.addWorksheet(worksheetName); await excelManager.writeWorksheet(worksheetName, worksheetData.data); await excelManager.autoFitColumnWidths(worksheetName, { minWidth: 80, maxWidth: 200 }); return { success: true, message: `Created loan analysis for $${args.loanAmount} at ${args.annualRate * 100}% for ${args.years} years`, data: { worksheetName, loanAmount: args.loanAmount, annualRate: args.annualRate, years: args.years } }; } catch (error) { return { success: false, error: error instanceof Error ? error.message : String(error) }; } }
- src/tools/excel-tools.ts:641-649 (schema)Input schema defining parameters for loan amount, interest rate, term years, and optional worksheet name.inputSchema: { type: "object", properties: { loanAmount: { type: "number", description: "Principal loan amount" }, annualRate: { type: "number", description: "Annual interest rate (e.g., 0.05 for 5%)" }, years: { type: "number", description: "Loan term in years" }, worksheetName: { type: "string", default: "Loan Analysis" } }, required: ["loanAmount", "annualRate", "years"]
- src/tools/excel-tools.ts:638-677 (registration)Tool registration object in excelTools array, including name, description, schema, and handler.{ name: "excel_create_loan_analysis", description: "Create a loan amortization analysis worksheet with payment schedule and accounting entries", inputSchema: { type: "object", properties: { loanAmount: { type: "number", description: "Principal loan amount" }, annualRate: { type: "number", description: "Annual interest rate (e.g., 0.05 for 5%)" }, years: { type: "number", description: "Loan term in years" }, worksheetName: { type: "string", default: "Loan Analysis" } }, required: ["loanAmount", "annualRate", "years"] }, handler: async (args: any): Promise<ToolResult> => { try { const worksheetData = ProfessionalTemplates.createLoanAnalysisWorksheet(args.loanAmount, args.annualRate, args.years); const worksheetName = args.worksheetName || "Loan Analysis"; await excelManager.addWorksheet(worksheetName); await excelManager.writeWorksheet(worksheetName, worksheetData.data); await excelManager.autoFitColumnWidths(worksheetName, { minWidth: 80, maxWidth: 200 }); return { success: true, message: `Created loan analysis for $${args.loanAmount} at ${args.annualRate * 100}% for ${args.years} years`, data: { worksheetName, loanAmount: args.loanAmount, annualRate: args.annualRate, years: args.years } }; } catch (error) { return { success: false, error: error instanceof Error ? error.message : String(error) }; } } },
- Helper method that generates the detailed loan analysis worksheet data structure, including amortization schedule, PMT formula, summaries, validation checks, and GAAP references.static createLoanAnalysisWorksheet(loanAmount: number, annualRate: number, years: number): WorksheetData { const monthlyRate = annualRate / 12; const totalPayments = years * 12; return { name: 'Loan Analysis', data: [ ['LOAN AMORTIZATION ANALYSIS', '', '', '', '', ''], ['Generated by Excel Finance MCP', '', '', '', '', ''], ['', '', '', '', '', ''], ['LOAN TERMS:', '', '', '', '', ''], ['Principal Amount:', { formula: `=${loanAmount}`, value: null }, '', 'User Input', 'GAAP ASC 835-20', 'https://www.fasb.org/asc/835/835-20/'], ['Annual Interest Rate:', { formula: `=${annualRate}`, value: null }, '', 'User Input', 'Market Rate', ''], ['Loan Term (Years):', { formula: `=${years}`, value: null }, '', 'User Input', '', ''], ['Payment Frequency:', 'Monthly', '', 'Standard', '', ''], ['', '', '', '', '', ''], ['CALCULATED PAYMENT:', '', '', '', '', ''], ['Monthly Payment:', { formula: `=PMT(${monthlyRate},${totalPayments},-B5)`, value: null }, '', 'PMT Function', 'Standard Amortization', 'https://www.fasb.org/asc/835/'], ['Total Interest:', { formula: `=B11*${totalPayments}-B5`, value: null }, '', 'Total Payments - Principal', '', ''], ['Total of Payments:', { formula: `=B11*${totalPayments}`, value: null }, '', 'Payment * Number of Payments', '', ''], ['', '', '', '', '', ''], ['AMORTIZATION SCHEDULE (First 12 Payments):', '', '', '', '', ''], ['Payment #', 'Payment Amount', 'Principal', 'Interest', 'Balance', 'Cumulative Interest'], ['0', '', '', '', { formula: `=${loanAmount}`, value: null }, ''], // Generate first 12 payment rows ...Array.from({ length: 12 }, (_, i) => { const paymentNum = i + 1; return [ paymentNum, { formula: `=$B$11`, value: null }, { formula: `=$B$11-D${17 + i}`, value: null }, { formula: `=E${16 + i}*$B$6/12`, value: null }, { formula: `=E${16 + i}-C${17 + i}`, value: null }, { formula: `=F${16 + i}+D${17 + i}`, value: null } ]; }), ['', '', '', '', '', ''], ['ANNUAL SUMMARY:', '', '', '', '', ''], ['Year 1 Interest:', { formula: '=SUM(D18:D29)', value: null }, '', 'Tax Deductible Interest', 'ASC 835-20-25', ''], ['Year 1 Principal:', { formula: '=SUM(C18:C29)', value: null }, '', 'Principal Reduction', '', ''], ['', '', '', '', '', ''], ['VALIDATION CHECKS:', '', '', '', '', ''], ['Payment Calculation:', { formula: '=IF(ABS(B11-PMT(B6/12,B7*12,-B5))<0.01,"CORRECT","ERROR")', value: '' }, '', 'Verify PMT formula', '', ''], ['Balance Check:', { formula: '=IF(ABS(E29-(B5-SUM(C18:C29)))<0.01,"CORRECT","ERROR")', value: '' }, '', 'Verify remaining balance', '', ''], ['', '', '', '', '', ''], ['ACCOUNTING TREATMENT:', '', '', '', '', ''], ['Monthly Journal Entry:', '', '', '', '', ''], [' Dr. Interest Expense', { formula: '=D18', value: null }, '', 'Monthly interest portion', '', ''], [' Dr. Loan Principal', { formula: '=C18', value: null }, '', 'Monthly principal portion', '', ''], [' Cr. Cash', { formula: '=B18', value: null }, '', 'Total monthly payment', '', ''], ['', '', '', '', '', ''], ['References:', '', '', '', '', ''], ['- FASB ASC 835: Interest accounting', '', '', '', '', ''], ['- FASB ASC 470: Debt classification', '', '', '', '', ''], ['- SEC Staff Accounting Bulletin 74', '', '', '', '', ''] ] }; }
- src/index.ts:32-44 (registration)Main tool registration where excelTools (containing excel_create_loan_analysis) is combined into allTools array used by MCP server for list and call handling.const allTools = [ ...excelTools, ...financialTools, ...rentalTools, ...expenseTools, ...reportingTools, ...cashFlowTools, ...taxTools, ...analyticsTools, ...chartTools, ...complianceTools, ...propertyTools, ];