Why? Mainly because I didn't have the cash in hand to pay for the car in one lump sum, but I knew that I would after 6 months (because after 10 years of being a student, I was finally going to have a job).

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.I originally created this spreadsheet to figure out a payment schedule for a car loan or auto loan. LoanLifeYrs = InputBox('Input Loan life (Years):')Ĭells(4, 2).Value = Format(intRateYrs, '#.#') & ' %'Ĭells(4, 3).Value = Format(intRateMths, 'Percent')Ĭells(6, 2).Value = Format(initLoan, 'Currency') IntRateYrs = InputBox('Input Interest rate (Annual):') Sub LoanSchedule()ĭim intRate, loanLife, initLoan, payment As Doubleĭim yearBegBal, intComp, prinComp, yearEndBal, intTot, prinTot, fvloan As Currency Here is a working version of what the OP was trying to do.I needed it myself. 'Year-beg Bal Annual Payment Interest Component Prinicipal Repaid Year-end Balĭim yearBegBal, intComp, prinComp, yearEndBalĬells(outRow + rowNum, 2).Value = yearBegBalĬells(outRow + rowNum, 3).Value = paymentĬells(outRow + rowNum, 4).Value = intCompĬells(outRow + rowNum, 5).Value = prinCompĬells(outRow + rowNum, 6).Value = yearEndBalĪs I am a beginner to VBA, would someone be able to help me please. Payment = Pmt(intRate, loanLife, -initLoan) InitLoan = InputBox('Input Loan amount:') IntRate = InputBox('Input Interest rate:') I've used the below VBA code on excel 2010, but it is not displaying the table correctly, this is the code that I've used Sub LoanSchedule() I need to generate a loan table for an interest rate 8%, loan life 15 years, and initial loan amount £200,000.

Loan amortization schedule excel