Macro coding for PMT Formula

[JaydeepBijlani]

Hello I am trying below VB code to execute PMT formula to calculate EMI but getting error "Unable to get pmt property of the worksheetFunction class"

Sub EMI()
Dim Loan_Amount As Double
Dim EMI As Double
Dim Rate As Double
Dim Payment_Per_Year As Double
Dim Tenure As Double
monthly_rate = Cells(7, 4).Value / Cells(8, 4).Value
Loan_Amount = Cells(5, 4).Value
Payment_Per_Year = Cells(8, 4).Value
number_of_periods = Cells(6, 4).Value * Cells(8, 4).Value
EMI = Application.WorksheetFunction.PMT(monthly_rate / 100, number_of_periods, Loan_Amount)
Range("D9").Value = EMI


End Sub

Please let me know if i am making any mistake

 

[Domenic]

Try running the code again. This time, though, when the error occurs, click on Debug. The line causing the error should be highlighted in yellow. Now, place your cursor over each of the variables (ie. monthly_rate, number_of_periods, and Loan_Amount), and see what each of them have been assigned. Maybe this might provide you with some clues.

 

[JaydeepBijlani]

Proper Values were not assigned that's error message was displayed. Thank you for your help.