vba 贷款摊销时间表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11934272/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loan amortization schedule
提问by Sahana
I need to generate a loan table for an interest rate 8%, loan life 15 years, and initial loan amount ￡200,000. 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;
我需要生成一个贷款表，利率为 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
as I am a beginner to VBA, would someone be able to help me please.
因为我是 VBA 的初学者，有人可以帮助我吗？
回答by Bharat Sinha
The value of LoanAmtBalis not defined and used directly, which I believe is causing the issues...
的值LoanAmtBal没有直接定义和使用，我认为这会导致问题......
yearBegBal = LoanAmtBal
回答by darkdimension
Here is a working version of what the OP was trying to do...I needed it myself.
这是 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
