So multiply it with 52, 26, 12 or 4 based on your periodic payments. In cell B3 we have the loan period in years. Quarterly Interest Rate = Yearly Interest Rate / 4 Monthly Interest Rate = Yearly Interest Rate / 12 (this what I have used in the formula in cell B5) Weekly Interest Rate = Yearly Interest Rate / 52įortnightly Interest Rate = Yearly Interest Rate / 26
#EXCEL STUDENT LOAN EXTRA PAYMENT CALCULATOR HOW TO#
Let me explain how to change that based on your periodic payments. Here in cell B2 we have the annual interest rate. Many people think it’s tough to change the monthly periodic payment in financial functions. =$B$1-sum($F$6:F6)įollow the above steps to prepare an amortization schedule in Google Sheets.įinished Table: Tips to Change Amortization Schedule Payment to Weekly, Fortnightly, or Quarterly Basis
In cell G6 enter the below formula and drag down.
Here is the array version of the PPMT formula. You can use any of the above formulas in cell F6 and drag/copy down. So instead of PPMT, the below basic formula would also return the correct result. Principal Payment Calculation Using PPMT:Įnter the below PPMT formula in cell F6: =-ppmt($B$2/12,C6,$B$3*12,$B$1)
I am including the non-array formula to make you understand the formula better. You can also convert the above IMPT formula to an expanding array formula. You can use the below IPMT formula in cell E6 and drag down until it reach the cell E29. Just enter this PMT formula in cell D6 and let it auto expand to cell D29! =ArrayFormula(if(len(C6:C),-pmt($B$2/12,$B$3*12,$B$1),)) Since the payment is the same for each period (constant periodic payments) I have used an array formula to fill all the 24 periods with the PMT value. So as an alternative I am converting the above PMT formula as an array formula. You need to drag this formula down until the cell D29. In cell D6, we can use the below PMT formula to calculate the monthly payments. Payment Calculation (Monthly Payments Including Interest + Principal Payment): In cell C6, enter the below ROW formula to get the number of periods in sequential order. That means there are 24 periodic payments. In my example, the loan period is set to 2 years in cell B3. The Formulas to Create an Amortization Schedule in Google Sheets We need a 5 column table to enter the formulas. Step 2: Preparing Amortization Table Format. Please arrange the values as per the below image. Step 1: Input Values in Amortization Schedule. Here are the steps to create an amortization schedule/table in Google Sheets.
No matter your periodic payments are on a weekly, fortnightly, quarterly, or monthly basis, the same formulas would help.įirst I will explain to you how to create an amortization schedule in Google Sheets that involves monthly (periodic) payments. We can use some of the financial functions in Google Sheets to create a loan amortization schedule easily. Amortization Schedule Calculation in Google Sheets Using the Built-in Functions Please note that the below two amortization schedules are based on constant amount periodic payments and that at a constant interest rate.
0 kommentar(er)
