Loan Calculation

[Helen1974]

I need to find a formula to help with the following.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If i loan money in the following manner and if the loan amount of loan one is a variable amount, as is the interest and the laon period, how can i calculate the value of my investment ? over any period of time, such as its value in 6, 12, 18, 24 months or longer if desired.<o></o>
<o></o>
Examples<o></o>
<o></o>
If i ONLY loan €100.00 to a client over 10 weeks at an interest rate of 10%, the client will make a weekly payment of €11.00.<o></o>
<o></o>
On week no 2 i will receive €11 from client number 1, which i will lend to client number 2 at the same rate of interest and for the same period of time. This client will make a weekly payment of €1.21.<o></o>
<o></o>
On week no 3 i will receive a payment from client 1 of €11.00 and a payment from client 2 of €1.21, totalling €12.21. This will be loaned to client number 3 at the same rate and time period as the above other clients and the client will make a weekly payment of €1.34.<o></o>
<o></o>
This will continue over an unspecified period of loans, clients and time.<o></o>

 

[shg]

Paste the code in a code and run macro LoanShark.

Adding a Macro to a Code Module<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
1. Copy the code from the post<o></o>
2. Press Alt+F11 to open the Visual Basic Editor (VBE)<o></o>
3. From the menu bar in the VBE window, do Insert > Module<o></o>
4. Paste the code in the window that opens<o></o>
5. Press Alt+Q to close the VBE and return to Excel<o></o>
<o> </o>
Running a Macro<o></o>
1. Press Alt+F8 to open the macro dialog<o></o>
2. Select the macro name from the dropdown list and press Run<o></o>

 

[MickG]

Helen, This could help :-
References And Code Libraries
The Dictionary object is part of the Microsoft Scripting Runtime Library, so your project must reference this library. In VBA, go to the Tools menu, choose References, and scroll to and select "Microsoft Scripting Runtime".

 

[shg]

I apologize, Helen, I should have mentioned that.

Thanks, Mick.

 

[Helen1974]

Guys I really do appreciate all you have been trying to do for me in order to get this program up and running, after following the instructions I am not getting any options to open a macro and to choose run, just wondering would this work better if you placed it into a spreadsheet and forward it to my email address for me to open on this end, you guy's are pretty clever will all this VBE, MACROS etc etc etc, this is a really new area to me and nothing to do with my hair colour at all , if that is acceptable to you, then my addy is helenp1974@hotmail.co.uk , look forward to hearing from you.

 

[shg]

Helen,

I uploaded it to http://www.box.net/shared/lorzjyrp3u

You need to enable macros, then press the Run button.

 

[Helen1974]

Any chance of your first name, I dont really like your username that much. its not fitting for a person with your ability.

Your code is absolutely brilliant, it is calculating the figures perfectly, I am also able to open the VBE and within the code I can change the Int Rate, Loan Amt and Payment Period, now if I had a user input screen where i could just enter these figures and press RUN to reveal my predictions I would be the happiest person in the world .

Really big thank you xxx

 

[Helen1974]

Got sorted Richard, thank you so much for your help.

 

[Helen1974]

Is your name name Mick ?

 

[shg]

Try the same link again, Helen.

 

[Helen1974]

Cheers my man