Generate a Loan EMI Schedule as an Excel Document Using C#
Live Chat Icon For mobile
Live Chat Icon
Popular Categories.NET  (179).NET Core  (29).NET MAUI  (214)Angular  (110)ASP.NET  (51)ASP.NET Core  (83)ASP.NET MVC  (90)Azure  (41)Black Friday Deal  (1)Blazor  (226)BoldSign  (15)DocIO  (24)Essential JS 2  (109)Essential Studio  (200)File Formats  (68)Flutter  (133)JavaScript  (226)Microsoft  (120)PDF  (81)Python  (1)React  (105)Streamlit  (1)Succinctly series  (131)Syncfusion  (942)TypeScript  (33)Uno Platform  (3)UWP  (4)Vue  (45)Webinar  (52)Windows Forms  (61)WinUI  (71)WPF  (163)Xamarin  (161)XlsIO  (38)Other CategoriesBarcode  (5)BI  (29)Bold BI  (8)Bold Reports  (2)Build conference  (9)Business intelligence  (55)Button  (4)C#  (157)Chart  (140)Chart of the week  (54)Cloud  (15)Company  (443)Dashboard  (8)Data Science  (3)Data Validation  (8)DataGrid  (70)Development  (658)Doc  (8)DockingManager  (1)eBook  (99)Enterprise  (22)Entity Framework  (7)Essential Tools  (14)Excel  (43)Extensions  (22)File Manager  (7)Gantt  (19)Gauge  (12)Git  (5)Grid  (31)HTML  (13)Installer  (2)Knockout  (2)Language  (1)LINQPad  (1)Linux  (2)M-Commerce  (1)Metro Studio  (11)Mobile  (512)Mobile MVC  (9)OLAP server  (1)Open source  (1)Orubase  (12)Partners  (21)PDF viewer  (43)Performance  (12)PHP  (2)PivotGrid  (4)Predictive Analytics  (6)Report Server  (3)Reporting  (10)Reporting / Back Office  (11)Rich Text Editor  (12)Road Map  (12)Scheduler  (54)Security  (4)SfDataGrid  (9)Silverlight  (21)Sneak Peek  (31)Solution Services  (4)Spreadsheet  (11)SQL  (14)Stock Chart  (1)Surface  (4)Tablets  (5)Theme  (12)Tips and Tricks  (112)UI  (393)Uncategorized  (68)Unix  (2)User interface  (68)Visual State Manager  (2)Visual Studio  (31)Visual Studio Code  (19)Web  (614)What's new  (333)Windows 8  (19)Windows App  (2)Windows Phone  (15)Windows Phone 7  (9)WinRT  (26)
Generate a Loan EMI Schedule as an Excel Document Using C#

Generate a Loan EMI Schedule as an Excel Document Using C#

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

Enjoy a smooth experience with Syncfusion’s Excel Library! Get started with a few lines of code and without Microsoft or interop dependencies.

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 is highly recommended.

  1. First, create a new console app (.NET Core) in Visual Studio by navigating to File > New > Project in the C# section.Creating console app in visual studio
  2. Then, install the Syncfusion.XlsIO.Net.Core NuGet package.Syncfusion.XlsIO.Net.Core NuGet installation
  3. Add the following code to the Program.cs file to generate the loan EMI schedule as an Excel document.
    /// <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.

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

    Console window output screenshot
    Console Window

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

    Generating Loan EMI Schedule as an Excel Document
    Generating Loan EMI Schedule as an Excel Document

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.

From simple data tables to complex financial models, Syncfusion empowers you to unleash your creativity and design stunning Excel spreadsheets.

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. Use it to generate high-performance Excel reports and process large data. Take a moment to peruse the documentation, where you’ll find other options and features, all with accompanying code samples.

Using this library, you can export Excel data to PDFsimagesdata tablesCSVTSV, collections of objectsODS, and other file formats.

Are you already a Syncfusion user? You can download the product setup here. If you’re not a Syncfusion user, you can download a free 30-day trial 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 portalsupport forum, or feedback portal. We are always happy to assist you!

Related blogs

Tags:

Share this post:

Popular Now

Be the first to get updates

Subscribe RSS feed

Be the first to get updates

Subscribe RSS feed