Generate a Loan EMI Schedule as an Excel Document Using C#
Tags: fileformats, csharp, excel
Published: 2023-08-10 15:06:36

---
title: Generate a Loan EMI Schedule as an Excel Document Using C#
published: true
date: 2023-08-10 10:27:55 UTC
tags: fileformats, csharp, excel
canonical_url: https://www.syncfusion.com/blogs/post/loan-emi-schedule-excel-csharp.aspx
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g1cd339kw0zn3rcbv8z5.png
---
As a developer, you may need to generate loan EMI (equated monthly installment) schedules as part of your finance applications. An EMI schedule is a table that shows the amount of money that needs to be paid each month towards a loan. Generating this EMI schedule in an Excel document will provide additional UI features that enhance the data’s readability.

The [Syncfusion Excel (XlsIO) Library](https://www.syncfusion.com/document-processing/excel-framework/net ".NET Excel Library") is a high-performance .NET Excel framework. This library allows you to create, read, and edit Microsoft Excel files in any .NET app. It also provides powerful conversion APIs that convert Excel files to PDF, images, and other formats.

In this blog, we’ll see how to generate a loan EMI schedule as an Excel document in C# using the Syncfusion .NET Excel Library.

## Generate a loan EMI schedule in an Excel document using C#

We’ll create an Excel document, calculate the EMI using the PMT formula, and then populate the EMI schedule into the Excel document by following these steps:

**Note:** If you are new to our Excel Library, following our [Getting Started guide](https://help.syncfusion.com/file-formats/xlsio/getting-started-create-excel-file-csharp-vbnet "Getting Started - Create Excel File in C# and VB.NET") is highly recommended.

1.First, create a new console app (.NET Core) in [Visual Studio](https://visualstudio.microsoft.com/downloads/ "Visual Studio") by navigating to **File > New > Project** in the **C#** section. ![Creating console app in visual studio](https://www.syncfusion.com/blogs/wp-content/uploads/2023/08/Creating-console-app-in-visual-studio.-1.png)
2.Then, install the [Syncfusion.XlsIO.Net.Core](https://www.nuget.org/packages/Syncfusion.XlsIO.Net.Core "Syncfusion.XlsIO.Net.Core NuGet package") NuGet package. ![Syncfusion.XlsIO.Net.Core NuGet installation](https://www.syncfusion.com/blogs/wp-content/uploads/2023/08/Syncfusion.XlsIO_.Net_.Core-NuGet-installation..png)
3.Add the following code to the **Program.cs** file to generate the loan EMI schedule as an Excel document.
```csharp
/// <summary>
/// Generates the loan schedule Excel document.
/// </summary>
private void GenerateLoanEMISchedule()
{
    // Initialize Excel Engine.
    using (ExcelEngine excelEngine = new ExcelEngine())
    {
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Xlsx;
        
        // Create a new workbook and worksheet.
        IWorkbook workbook = application.Workbooks.Create(1);
        IWorksheet sheet = workbook.Worksheets[0];
        
        // Get loan details from the user.
        GetLoanDetails();
        
        // Calculate EMI.
        CalculateEMI(sheet, m_BankName, m_AccountNumber, m_CustomerName, m_InterestRate, m_LoanAmount, m_Tenure, m_BorrowedDate);
        
        // Display the EMI amount.
        Console.WriteLine("Your EMI amount is.." + sheet["F10"].DisplayText);
        
        // Save workbook and close stream.
        Directory.CreateDirectory("../../../GeneratedOutput");
        FileStream generatedExcel = new FileStream("../../../GeneratedOutput/Loan EMI Schedule.xlsx", FileMode.Create, FileAccess.Write);
        workbook.Version = ExcelVersion.Xlsx;
        workbook.SaveAs(generatedExcel);
        generatedExcel.Close();
        
        Console.WriteLine("Excel document generated successfully..");
    }
}
```

We’ll calculate the EMI using the **PMT** function in Microsoft Excel.

```csharp
/// <summary>
/// Calculate EMI and generate EMI schedule.
/// </summary>
/// <param name="sheet">Worksheet</param>
private static void CalculateEMI(IWorksheet sheet, string bankName, long accountNumber, string customerName, double interestRate, long loanAmount, int tenureInMonths, DateTime borrowedDate)
{
    sheet["A1"].Value = bankName;
    sheet["A4"].Value = "Loan EMI Schedule";
    sheet["A6"].Value = "Customer Name";
    sheet["A8"].Value = "Account Number";
    sheet["A10"].Value = "Tenure in months";
    sheet["A12"].Value = "Interest";
    
    sheet["B6"].Text = customerName;
    sheet["B8"].Number = accountNumber;
    sheet["B10"].Number = tenureInMonths;
    sheet["B12"].Number = interestRate/100;
    
    sheet["E6"].Value = "Loan Amount";
    sheet["E8"].Value = "Frequency";
    sheet["E10"].Value = "EMI Amount";
    sheet["E12"].Value = "Borrowed Date";
    
    sheet["F6"].Number = loanAmount;
    sheet["F8"].Value = "Monthly";
    sheet["F12"].DateTime = borrowedDate;
    
    sheet["A15"].Value = "Payment No.";
    sheet["B15"].Value = "Date";
    sheet["C15"].Value = "Payment";
    sheet["D15"].Value = "Principle";
    sheet["E15"].Value = "Interest";
    sheet["F15"].Value = "Outstanding Principle";
    
    sheet.Workbook.Names.Add("Interest", sheet["B12"]);
    sheet.Workbook.Names.Add("Tenure", sheet["B10"]);
    sheet.Workbook.Names.Add("LoanAmount", sheet["F6"]);
    sheet.Workbook.Names.Add("BorrowedDate", sheet["F12"]);
    
    sheet["F10"].Formula = "=-PMT(Interest/12,Tenure, LoanAmount)";
    sheet.EnableSheetCalculations();
    
    double emi = double.Parse(sheet["F10"].CalculatedValue.ToString());
    double balance = loanAmount;
    double totalInterestPaid = 0;
    
    for (int i = 1; i <= tenureInMonths; i++)
    {
        double interest = balance * (interestRate/100)/12;
        double principal = emi - interest;
        balance -= principal;
        
        totalInterestPaid += interest;
        
        sheet[15 + i, 1].Number = i;
        sheet[15 + i, 2].Formula = "=EDATE(BorrowedDate," + i + ")";
        sheet[15 + i, 3].Number = emi;
        sheet[15 + i, 4].Number = principal;
        sheet[15 + i, 5].Number = interest;
        sheet[15 + i, 6].Number = balance;
    }
    
    IRange used = sheet.UsedRange;
    
    sheet[used.LastRow + 2, 4, used.LastRow + 2, 5].Merge();
    sheet[used.LastRow + 2, 4, used.LastRow + 2, 5].CellStyle.Font.Bold = true;
    sheet[used.LastRow + 2, 4, used.LastRow + 2, 5].Value = "Principle";
    sheet[used.LastRow + 3, 4, used.LastRow + 3, 5].Merge();
    sheet[used.LastRow + 3, 4, used.LastRow + 3, 5].CellStyle.Font.Bold = true;
    sheet[used.LastRow + 3, 4, used.LastRow + 3, 5].Value = "Interest";
    sheet[used.LastRow + 4, 4, used.LastRow + 4, 5].Merge();
    sheet[used.LastRow + 4, 4, used.LastRow + 4, 5].CellStyle.Font.Bold = true;
    sheet[used.LastRow + 4, 4, used.LastRow + 4, 5].Value = "Total Amount";
    
    sheet[used.LastRow + 2, 6, used.LastRow + 2, 6].Number = loanAmount;
    sheet[used.LastRow + 2, 6, used.LastRow + 2, 6].NumberFormat = "$#,###.00";
    sheet[used.LastRow + 3, 6, used.LastRow + 3, 6].Number = totalInterestPaid;
    sheet[used.LastRow + 3, 6, used.LastRow + 3, 6].NumberFormat = "$#,###.00";
    sheet[used.LastRow + 4, 6, used.LastRow + 4, 6].Number = totalInterestPaid + loanAmount;
    sheet[used.LastRow + 4, 6, used.LastRow + 4, 6].NumberFormat = "$#,###.00";
    
    //Apply styles to the cells.
    sheet.IsGridLinesVisible = false;
    
    sheet["A1:F2"].Merge();
    IStyle mergeArea = sheet["A1"].MergeArea.CellStyle;
    mergeArea.Font.Size = 18;
    mergeArea.Font.Bold = true;
    mergeArea.Font.Underline = ExcelUnderline.Single;
    
    sheet["A4:F4"].Merge();
    sheet["A4"].Value = "Loan EMI Schedule";
    mergeArea = sheet["A4"].MergeArea.CellStyle;
    mergeArea.Font.Size = 16;
    mergeArea.Font.Bold = true;
    
    sheet["A6:A12"].CellStyle.Font.Bold = true;
    sheet["E6:E12"].CellStyle.Font.Bold = true;
    sheet["F6"].NumberFormat = "$#,###.00";
    sheet["F10"].NumberFormat = "$#,###.00";
    
   sheet["B12"].NumberFormat = "0.0%";
    sheet["F12"].NumberFormat = Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern;
    
    sheet["A15:F15"].CellStyle.Font.Bold = true;
    sheet["A15:F15"].WrapText = true;
    sheet["A15:F15"].RowHeight = 31;           
    
    sheet.UsedRange.ColumnWidth = 15.5;
    
    used = sheet.UsedRange;
    
    sheet[16,2, used.LastRow - 4, 2].NumberFormat = Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern;
    sheet[16, 3, used.LastRow - 4, 6].NumberFormat = "$#,###.00";
    
    sheet[15, 1, 15, 6].BorderAround(ExcelLineStyle.Thin);
    sheet[15, 1, 15, 6].BorderInside();
    sheet[16, 1, used.LastRow - 4, 6].BorderAround(ExcelLineStyle.Thin);
    sheet[16, 1, used.LastRow - 4, 6].Borders[ExcelBordersIndex.InsideVertical].LineStyle = ExcelLineStyle.Thin;
    
    sheet[6, 1, used.LastRow, 6].CellStyle.Font.Size = 12;
    
    sheet[16, 1, used.LastRow, 6].RowHeight = 24;
    sheet[1, 1, used.LastRow, 6].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
    sheet[1, 1, used.LastRow, 6].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;            
}
```

After running the sample with proper details, the console window will look the following image.
<figure>

  <img src="https://www.syncfusion.com/blogs/wp-content/uploads/2023/08/Console-window-output-screenshot.png" alt="Console window output screenshot" style="width:100%">

  <figcaption>Console window</figcaption>

</figure>

Now, the output Excel document will look like the following image.
<figure>

  <img src="https://www.syncfusion.com/blogs/wp-content/uploads/2023/08/Generating-Loan-EMI-Schedule-as-an-Excel-Document.png" alt="Generating Loan EMI Schedule as an Excel Document" style="width:100%">

  <figcaption>Generating Loan EMI Schedule as an Excel Document</figcaption>

</figure>

## GitHub reference

You can download the complete code snippet for generating a loan EMI schedule in an Excel document using C# from this [GitHub repository](https://github.com/SyncfusionExamples/XlsIO-Examples/tree/master/Real%20Time%20Examples%20in%20Excel/Loan%20EMI%20Schedule "Loan EMI Schedule in Excel using C# GitHub demo").

## Conclusion

Thanks for reading! In this blog, we’ve seen how to generate a loan EMI schedule as an Excel document using the [Syncfusion .NET Excel Library](https://www.syncfusion.com/excel-framework/net ".NET Excel Library"). Use it to generate high-performance Excel reports and process large data. Take a moment to peruse the [documentation](https://help.syncfusion.com/file-formats/xlsio/overview "Getting started with Syncfusion Excel (XlsIO) Library"), where you’ll find other options and features, all with accompanying code samples.

Using this library, you can export Excel data to [PDFs](https://help.syncfusion.com/file-formats/xlsio/excel-to-pdf-conversion "Excel to PDF conversion"), [images](https://help.syncfusion.com/file-formats/xlsio/worksheet-to-image-conversion "Worksheet to Image conversion"), [data tables](https://help.syncfusion.com/file-formats/xlsio/working-with-data#exporting-from-worksheet-to-data-table " Exporting from Worksheet to Data Table"), [CSV](https://www.syncfusion.com/kb/9092/convert-excel-file-to-csv-in-c-vb-net "Converting Excel file to CSV"), [TSV](https://www.syncfusion.com/kb/8556/does-xlsio-support-tsv-files "Excel to TSV conversion"), [collections of objects](https://help.syncfusion.com/file-formats/xlsio/working-with-data#exporting-from-worksheet-to-collection-objects "Working with Excel Data"), [ODS](https://help.syncfusion.com/file-formats/xlsio/excel-to-ods-conversion "Excel to ODS conversion"), and other file formats.

Are you already a Syncfusion user? You can download the product setup [here](https://www.syncfusion.com/account/downloads/studio/ "Essential Studio License and Download page"). If you’re not a Syncfusion user, you can download a [free 30-day trial](https://www.syncfusion.com/downloads/fileformats/confirm "Get the free evaluation of the Essential Studio for File Formats") of Essential Studio to try out this control.

Please let us know in the comments below if you have any questions about these features. You can also contact us through our [support portal](https://support.syncfusion.com/ "Syncfusion Support Portal"), [support forum](https://www.syncfusion.com/forums?_ga=2.160385213.108886952.1555906495-214292665.1551328372 "Syncfusion Support Forum"), or [feedback portal](https://www.syncfusion.com/feedback/ "Syncfusion Feedback Portal"). We are always happy to assist you!

## Related blogs

- [Export Data to a Predefined Excel Template in C#](https://www.syncfusion.com/blogs/post/export-data-to-a-predefined-excel-template-in-c.aspx "Blog: Export Data to a Predefined Excel Template in C#")
- [6 Easy Ways to Export Data to Excel in C#](https://www.syncfusion.com/blogs/post/6-easy-ways-to-export-data-to-excel-in-c-sharp.aspx "Blog: 6 Easy Ways to Export Data to Excel in C#")
- [How to Export Data from SQL Server to Excel Table in C#](https://www.syncfusion.com/blogs/post/export-data-from-sql-server-to-excel-in-c-sharp.aspx "Blog: How to Export Data from SQL Server to Excel Table in C#")
- [Export Data from a Collection to Excel and Group It in C#](https://www.syncfusion.com/blogs/post/export-data-from-collection-to-excel-and-group-csharp.aspx "Blog: Export Data from a Collection to Excel and Group It in C#")
- [Easy Steps to Export HTML Tables to an Excel Worksheet in C#](https://www.syncfusion.com/blogs/post/easy-steps-to-export-html-tables-to-an-excel-worksheet-in-c.aspx "Blog: Easy Steps to Export HTML Tables to an Excel Worksheet in C#")