#Value! error - what's wrong with this function?

[bjornas]

I try to create my own function to calculate remaining periods on annuity mortgage. It goes like this:

Function perioder()
Dim PV As Double
Dim r As Double
Dim P As Double
Dim perioder As Double

perioder = Log(1 - ((PV * r) / P) ^ -1) / Log(1 + r)

End Function

However, when I call it in my worksheet, it only returns the #Value! error. Being new to Excel VBA programming, I can't figure out what is wrong.

Can anybody please help me?

Thanx a lot!

[Jonmo1]

You haven't provided any values to the variables PV r and p

And you don't dim perioder, that's the name of the function

Something like

Please Login or Register  to view this content.

Then you call it in your worksheet like
=perioder(10,20,30)
or whatever the values of each veriable should be..

[bjornas]

Thks for the help, but unfortunately it didn't remove the error.

The function is called in cell F8 this way "=perioder(f5,f6,f7)", with exactly the correct arguments. The numbers I put into the function somehow seems to have a wrong format.

[Jonmo1]

Works for me, as long as F5 F6 and F7 are all numeric values.

Can you post a sample book?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[bjornas]

Sure, thanks for your help.

Let me see if I get it. Data are completely rubbish, pls. only care about the outlined cells.

[Jonmo1]

The LOG function requires a POSITIVE # as it's argument.

However, with the numbers you suppllied in F5 F6 and F7,
Then this calculation results in a negative number
1 - ((PV * r) / P) ^ -1

That results in
-0.5838944

Now LOG(-0.5838944) = Error.

[bjornas]

Hmmm, now I get the #Name! error...What on earth am I doing wrong?

Jonmo1, can you pls upload your solution to my problem...?