Writing a VBA USER DEFINED code for DISCOUNTED PAYBACK METHOD

[NovaD]

Hi,
I need to write a UDC for Discounted Payback method. I wrote the code for the payback part of the problem but cannot do the discounted cashflows section. I need to write one code and cannot calculate the discounted CFs in Excel beforehand. Please see problem and info copied below.

Help please!

Function Payback(NumFutureCFs, Cost, RangeFutureCFs)

Dim loopcounter As Integer
Dim AccumulationCF As Single

AccumulationCF = Cost

For loopcounter = 1 To NumFutureCFs
AccumulationCF = AccumulationCF + RangeFutureCFs(loopcounter)

If AccumulationCF >= 0 Then Exit For

Next loopcounter
Payback = loopcounter - 1 + -(AccumulationCF - RangeFutureCFs(loopcounter)) / RangeFutureCFs(loopcounter)

Use your user defined function to solve for the discounted payback period on the following project. You want
to be select the raw cash flows. The function should discount them and determine the discounted
payback period.

Year cash flow

year 0 (10000)
Year 1 2000
Year 2 3000
Year 3 3500
Year 4 4000
Year 5 4000

Discount Rate 9%


End Function

[Richard Buttrey]

Hi, and welcome to the forum.

I'm going to assume this is a homework task. Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

I'll add the word Homework to your title to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints.

Although you are specifically asked to create a UDF nevertheless would you indicate what answer you are expecting so that those who are happy to help can understand the goal.

[NovaD]

Hi Richard,

Yes it is an assignment and would really have preferred to get tutoring on how to do it. I was finally able to do it myself though after trying for the past 36 hours but I thank you. Where on this website would I sign up for tutoring in the future and is it an hourly cost? Is it live tutoring? All information given regarding tutoring would be highly appreciated.

Thank you again.

Nova.

[Richard Buttrey]

Well done for working it out. All we ask is that homework be mentioned in a thread title.

I doubt there's any necessity to formally 'sign up' for tutoring. Just ask your questions in a normal thread.
Perhaps indicate you are looking to understand alternative approaches and their relative advantages and disadvantages, and ask for suggestions for optimum and efficient solutions. The way threads proceed can then lead to discussions akin to a tutorial.