贷款摊销时间表

Question

我需要为利率8％，贷款期限15年和初始贷款额200,000英镑生成贷款表。我在Excel 2010中使用了下面的VBA代码，但它没有正确显示表格，这是我用过的代码;

Sub LoanSchedule() 

Dim intRate, loanLife, initLoan, payment 

intRate = InputBox("Input Interest rate:") 
loanLife = InputBox("Input Loan life:") 
initLoan = InputBox("Input Loan amount:") 

Cells(4, 2).Value = intRate 
Cells(5, 2).Value = loanLife 
Cells(6, 2).Value = initLoan 

payment = Pmt(intRate, loanLife, -initLoan) 

'Year-beg Bal Annual Payment Interest Component Prinicipal Repaid Year-end Bal 
Dim yearBegBal, intComp, prinComp, yearEndBal 

outRow = 10 
yearBegBal = LoanAmtBal 

For rowNum = 1 To loanLife 
intComp = yearBegBal * intRate 
prinComp = payment - intComp 
yearEndBal = yearBegBal - prinComp 

Cells(outRow + rowNum, 1).Value = rowNum 
Cells(outRow + rowNum, 2).Value = yearBegBal 
Cells(outRow + rowNum, 3).Value = payment 
Cells(outRow + rowNum, 4).Value = intComp 
Cells(outRow + rowNum, 5).Value = prinComp 
Cells(outRow + rowNum, 6).Value = yearEndBal 

yearBegBal = yearEndBal 
Next rowNum 

End Sub 

因为我是VBA的初学者，请有人能够帮助我。

Answer 1

的LoanAmtBal值没有定义，并且直接使用，我相信这是造成问题...

yearBegBal = LoanAmtBal 

Answer 2

这里是什么样的OP试图做一个工作版本......我需要它自己。

Sub LoanSchedule() 

Dim intRate, loanLife, initLoan, payment As Double 
Dim yearBegBal, intComp, prinComp, yearEndBal, intTot, prinTot, fvloan As Currency 

ActiveSheet.UsedRange.Delete 

intRateYrs = InputBox("Input Interest rate (Annual):") 
loanLifeYrs = InputBox("Input Loan life (Years):") 
initLoan = InputBox("Input Loan amount:") 

Application.DisplayAlerts = True 
Application.ScreenUpdating = True 

intRateMths = (intRateYrs/100)/12 
loanLifeMths = loanLifeYrs * 12 

Cells(4, 2).Value = Format(intRateYrs, "#.##") & " %" 
Cells(4, 3).Value = Format(intRateMths, "Percent") 
Cells(5, 2).Value = loanLifeYrs 
Cells(5, 3).Value = loanLifeMths 
Cells(6, 2).Value = Format(initLoan, "Currency") 

payment = Pmt(intRateMths, loanLifeMths, -initLoan) 
Cells(7, 2).Value = Format(payment, "Currency") 

outRow = 10 
intTot = 0 
prinTot = 0 
fvloan = 0 

Cells(10, 2).Value = "Beginning Balance" 
Cells(10, 3).Value = "Payment" 
Cells(10, 4).Value = "Interest" 
Cells(10, 5).Value = "Principal" 
Cells(10, 6).Value = "End Balance" 
Cells(10, 7).Value = "Total Interest" 
Cells(10, 8).Value = "Total Principal" 
Cells(10, 9).Value = "Total Repaid" 
yearBegBal = initLoan 

For rowNum = 1 To loanLifeMths 
    intComp = yearBegBal * intRateMths 
    prinComp = payment - intComp 
    yearEndBal = yearBegBal - prinComp 

    intTot = intTot + intComp 
    prinTot = prinTot + prinComp 
    fvloan = intTot + prinTot 

    Cells(outRow + rowNum, 1).Value = rowNum 
    Cells(outRow + rowNum, 2).Value = Format(yearBegBal, "Currency") 
    Cells(outRow + rowNum, 3).Value = Format(payment, "Currency") 
    Cells(outRow + rowNum, 4).Value = Format(intComp, "Currency") 
    Cells(outRow + rowNum, 5).Value = Format(prinComp, "Currency") 
    Cells(outRow + rowNum, 6).Value = Format(yearEndBal, "Currency") 
    Cells(outRow + rowNum, 7).Value = Format(intTot, "Currency") 
    Cells(outRow + rowNum, 8).Value = Format(prinTot, "Currency") 
    Cells(outRow + rowNum, 9).Value = Format(fvloan, "Currency") 

    yearBegBal = yearEndBal 
Next rowNum 

ActiveSheet.Range("A:I").EntireColumn.AutoFit 
Rows("11:11").Select 
ActiveWindow.FreezePanes = True 
Range("A1").Select 

Application.DisplayAlerts = False 
Application.ScreenUpdating = False 

End Sub