Need some assistance with my syntax

[RHaze]

Hey guys, looking for some help on proper syntax for creating a function in the VBE of Excel. My apologies ahead of time, I am extremely new to this and the below problem is part of a Coursera course I am taking:



When a loan of principal amount, P, is taken at an annual interest rate iwith a repayment period of n years, the following equation provides the monthly payment, A:

FY1-6LYLEeiu9BLDf-7i5A_b9833a7ad547b30f277ed1717c0566bd_payment.png

Create a VBA function called payment(P,i,n) that will output the monthly payment (A) based on the principal, annual interest rate, and lifetime of the loan.

To check your answer, the monthly payment on a 20-year loan with principle $10,000 with an annual interest rate of 4.5% would be $63.26.



The way I wrote this function is below. When I try to run it, I get a "Compile error: Expected Array" error message. I know what I have is incorrect, just need help figuring it out:

Function payment(P As Double, i As Double, n As Double) As Double
Dim A As Double
A = (P(i / 12)) / (1 - (1 + (i / 12))) ^ (-n * 12)
payment = A
End Function

[carlmon]

Please Login or Register  to view this content.

[ByteMarks]

Reason for compile error is that the P( is interpreted as an array rather than a multiplication

Also I think you need to account for 4.5 being a percentage.

Please Login or Register  to view this content.

[RHaze]

Thank you ByteMarks! Very much appreciated.

Looks like that was the problem. And yes, also had to account for 4.5 being a percentage.

[RHaze]

Apologies, but my next question is how do I get "payment" into a currency format? I'm trying to use the FormatCurrency function, but can't figure it out.

[ByteMarks]

Maybe change

Payment = A to

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[adokhai]

Hi, please how did you go about the accounting for the 4.5 as a percentage?

[jolivanes]

Please Login or Register  to view this content.

[adokhai]

Please Login or Register  to view this content.

Thanks a lot, but it isn't working for me.

Function payment(P As Double, i As Double, n As Double) As Double
Dim A As Double
i = (i / 100)
A = (P * i / 12) / (1 - (1 + i / 12) ^ (-n * 12))
payment = A
End Function

This was how I did it, still giving me error message that "Function payment is incorrect"

[JohnTopley]

Works OK for me

=payment(10000,4.5,20)

returns 63.26

[adokhai]

Thanks a lot, I so appreciate the response. It's working for me now. However, when I try uploading it for grading I keep receiving an error message that the payment function is incorrect.

[MudassarHafeez12059]

I wrote the following syntax, but I am receiving an error message that payment function is incorrect.

Function payment(P As Double, i As Double, n As Double) As Double
Dim A As Double
i = (i / 100)
A = (P * (i / 12)) / (1 - (1 + i / 12) ^ (-n * 12))
payment = A
End Function