In this article, I’ll explain how to calculate those amounts for both types of loans. My visitor asked about cumulative payments of principal and interest. What Help Does Excel Provide for Each Type of Loan? But straight-line loans pay the same amount of principal each period plus a declining amount of interest.Ĥ. Here, as you can see, even-payment loans have the same payment every period. In How to Create Even-Payment and Straight-Line Amortization Tables in Excel, I used these two figures to show the trends in periodic payments for each type of loan. What Type of Loan is It? Even-Payment? Or Straight-Line? I’ll cover floating rate loans at another time. I’m going to assume that the interest rate is fixed. Is the Loan’s Interest Rate Fixed or Floating? Investopedia: A loan from a bank for a specific amount that has a specified repayment schedule and a fixed or floating interest rate.Ģ.Term loans usually last between one and ten years, but may last as long as 30 years in some cases. Wikipedia: A monetary loan that is repaid in regular payments over a set period of time.Term loans are generally provided as working capital for acquiring income producing assets (machinery, equipment, inventory) that generate the cash flows for repayment of the loan. Business Dictionary: Asset based short-term (usually for one to five years) loan payable in a fixed number of equal installments over the term of the loan. For example, here’s how different sources define a term loan: This is an interesting question because it touches on at least four issues related to the time value of money: 1. Implement thread.“How do I calculate cumulative principal and interest for term loans? I have scoured the web for a function that will perform this task, with no avail.Data Science vs Big Data vs Data Analytics.Lrow = Sheets(2).Range("A" & Rows.Count).End(xlUp).RowĬells(lrow + 4, 2).Value = Format(intRateYrs, "#.#") & " %"Ĭells(lrow + 4, 3).Value = Format(intRateMths, "Percent")Ĭells(lrow + 6, 2).Value = Format(initLoan, "Currency")Ĭells(lrow + 7, 2).Value = Format(payment, "Currency")Ĭells(lrow + 10, 2).Value = "Beginning Balance"Ĭells(lrow + 10, 6).Value = "End Balance"Ĭells(lrow + 10, 7).Value = "Total Interest"Ĭells(lrow + 10, 8).Value = "Total Principal"Ĭells(lrow + 10, 9).Value = "Total Repaid" 'initLoan = InputBox("Input Loan amount:") 'loanLifeYrs = InputBox("Input Loan life (Years):") 'intRateYrs = InputBox("Input Interest rate (Annual):") This is how the result looks like from one iterationįor Each cell In ThisWorkbook.Sheets(1).Range("B2:B2001") Payment = Pmt(intRateMths, loanLifeMths, -initLoan)Ĭells(7, 2).Value = Format(payment, "Currency")Ĭells(outRow + rowNum, 2).Value = Format(yearBegBal, "Currency")Ĭells(outRow + rowNum, 3).Value = Format(payment, "Currency")Ĭells(outRow + rowNum, 4).Value = Format(intComp, "Currency")Ĭells(outRow + rowNum, 5).Value = Format(prinComp, "Currency")Ĭells(outRow + rowNum, 6).Value = Format(yearEndBal, "Currency")Ĭells(outRow + rowNum, 7).Value = Format(intTot, "Currency")Ĭells(outRow + rowNum, 8).Value = Format(prinTot, "Currency")Ĭells(outRow + rowNum, 9).Value = Format(fvloan, "Currency")ĪctiveSheet.Range("A:I").EntireColumn.AutoFit InitLoan = InputBox("Input Loan amount:")Ĭells(4, 2).Value = Format(intRateYrs, "#.#") & " %"Ĭells(4, 3).Value = Format(intRateMths, "Percent")Ĭells(6, 2).Value = Format(initLoan, "Currency") LoanLifeYrs = InputBox("Input Loan life (Years):") IntRateYrs = InputBox("Input Interest rate (Annual):") Sub one()ĭim intRate, loanLife, initLoan, payment As Doubleĭim yearBegBal, intComp, prinComp, yearEndBal, intTot, prinTot, fvloan As Currency I am requesting changing the script below to take the values from 3 columns. I already have a functioning VBA script (shown below) to compute the amortization schedule, but instead of manually entering the data, I want it to take the inputs from the three columns, calculate it for 7328 rows, and add the numbers below each other. I have three columns Sanctioned Amount, Tenure, and Rate of Interest for 7328 rows. I am new to Excel VBA and looking for help in editing a macro.
0 Comments
Leave a Reply. |