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>

 

[Richard Schollar]

Hi

Since your reinvested amounts are at the same rate of interest you can just compound up the whole amount

eg €100 invested for 24 months in total, what is value of all your investments generated off of this in 12 months @ rate of 10% per month:

=100*(1+10%)^12

=313.84

Obviously adjusting periods/amounts/rates as required.

 

[Helen1974]

Hi Richard,

Thanks for your reply, unfortuneately i dont fully understand and am hoping you can set out an example, if you wish you could send it to my hotmail account @ helenp1974@hotmail.co.uk.

Thanks so far.

 

[Richard Schollar]

I sent you the file. Here it is below for anyone else:

Excel Workbook
	A	B	C	D	E	F	G	H
8		Loan 1	Loan 2	Loan 3	Loan 4	Loan 5	Loan 6	Loan 7
9		-100
10		10	-10
11		10	1	-11
12		10	1	1.1	-12.1
13		10	1	1.1	1.21	-13.31
14		10	1	1.1	1.21	1.331	-14.641
15		10	1	1.1	1.21	1.331	1.4641	-16.1051
16		110	11	12.1	13.31	14.641	16.1051	17.71561
17
18
19	Total return after 7 periods	194.8717
20
21	or directly:	194.8717
Sheet4

Excel 2010

Cell Formulas
Range	Formula
D12	=-D$11*10%
E12	=-SUM(B12:D12)
E13	=E$12*10%*-1
F13	=-SUM(B13:E13)
F14	=F$13*10%*-1
G14	=-SUM(B14:F14)
G15	=G$14*10%*-1
H15	=-SUM(B15:G15)
H16	=-1*H15*110%
B19	=SUM(B16:H16)
B21	=100*(1+10%)^7

 

[Helen1974]

What does, or where does the -1 derive from in cell g15. alos cell b21 shows 100*(1*30%)^7, what does this number represent ?.

Again Richard, a big thank you for your help and your patients,

 

[Richard Schollar]

The -1 just flips it from a negative to a positive inflow ie in the schedule above, you loaning money out to people is shown as a negative (outflow) whereas repayments of interest (and, ultimately capital too) is shown as a positive (inflow).

B21 is showing the affect of compounding 100 at an annualised rate of 10% for 7 years (strictly speaking it is showing the effect of compounding 100 at a periodic rate of 10% for 7 periods). The ^ symbol means "raise to the power of" so it is equivalent to saying:

=100*(1+10%)*(1+10%)*(1+10%)*(1+10%)*(1+10%)*(1+10%)*(1+10%)

=100*1.1*1.1*1.1*1.1*1.1*1.1*1.1

Why are you looking at this problem, if you don't mind me asking?

 

[Helen1974]

A friend has a money lending business, which they are not running very well and I am considering taking it over from them. I am doing my homework as much as is possible to ensure that I am looking a a profitable business, as I strongly believe that it has massive potential in the right hands.

You have been very helpful, thank you for that.

I now understand your explanations, I was getting confused over your use of the number 7 as a period, your number 7 represents "years" and I was using it as weeks which was showing massive returns. I created a more in depth spreadsheet to calculate exactly the weekly incomings and outgoings, your formula is much much quicker but is not verifying my understanding of things, If you dont mind i would like to send it to you via your email address, if you take a look you will have a far clearer understanding of what i need to achieve, I hope lol.

 

[shg]

Helen, if you loan $100, and the recipient makes 10 weekly payments of $11 to repay it, that's an interest rate of =RATE(10, 11, -100, 0, 0) ~ 1.77% per week, or ~ 149% per year. That's pretty stiff; you sure that's correct?

 

[shg]

Run this code on an empty worksheet:

Sub LoanShark()
    Const dInt      As Double = 0.1         ' one-time interest rate on principal
    Const nPmt0      As Long = 10           ' number of payments
    Const cRcpt0    As Currency = 100@      ' starting amount
    Dim dic         As Scripting.Dictionary ' the loan book
    Dim vKey        As Variant              ' a key to dic
 
    Dim nPmt        As Long                 ' # of pmts to retire loan
    Dim cPmt        As Currency             ' payment amount
    Dim cRcpt       As Currency             ' weekly receipts
    Dim cRcvb       As Currency             ' receivables
    Dim nLoan       As Long                 ' # loans outstanding
    Dim iWeek       As Long                 ' week counter
    Dim rOut        As Range                ' output range
 
    Set dic = New Scripting.Dictionary
    Set rOut = Range("A1:D1")
    rOut.Value = Array("Week", "Loans", "Receipts", "Receivables")
 
    With dic
        .Add Key:="Loan0", Item:=Array(nPmt0, CCur((1 + dInt) * cRcpt0 / nPmt0))
        Set rOut = rOut.Offset(1)
        rOut.Value = Array(0, .Count, 0@, CCur((1 + dInt) * cRcpt0))
 
        For iWeek = 1 To 104
            cRcpt = 0@
            cRcvb = 0@
            nLoan = .Count
 
            For Each vKey In .Keys
                nPmt = .Item(vKey)(0)
                cPmt = .Item(vKey)(1)
                cRcpt = cRcpt + cPmt
                If nPmt = 1 Then
                    .Remove vKey
                Else
                    .Item(vKey) = Array(nPmt - 1, cPmt)
                End If
 
                cRcvb = cRcvb + (nPmt - 1) * cPmt
            Next vKey
 
            ' make a new loan from the receipts
            .Add Key:="Loan" & iWeek, Item:=Array(nPmt0, CCur((1 + dInt) * cRcpt / nPmt0))
            cRcvb = cRcvb + (1 + dInt) * cRcpt
 
            Set rOut = rOut.Offset(1)
            rOut.Value = Array(iWeek, .Count, cRcpt, cRcvb)
        Next iWeek
    End With
End Sub

       -A-- --B-- ---C---- -----D-----
   1   Week Loans Receipts Receivables
   2      0     1 $0.00    $110.00    
   3      1     2 $11.00   $111.10    
   4      2     3 $12.21   $112.32    
   5      3     4 $13.55   $113.68    
   6      4     5 $15.04   $115.18    
   7      5     6 $16.70   $116.85    
   8      6     7 $18.54   $118.70    
   9      7     8 $20.57   $120.76    
  10      8     9 $22.84   $123.05    
  11      9    10 $25.35   $125.58    
  12     10    10 $28.14   $128.39    
  13     11    10 $20.23   $130.42    
  14     12    10 $21.25   $132.54

It shows the strategy you describe: roll weekly receipts immediately into a new loan with the same terms (11% of principal for 10 weeks).

When the week-over-week rate stabilizes, it's 1.77%/wk return.

Nice business, as long as you have Vinnie the Enforcer on hot standby!

 

[Helen1974]

First, do i just copy this code and paste it into a spreadsheet cell.

Secondly, are you free to be my enforcer,

Thirdly, Thanks a million for all your help.