Making my budget sheet easier to use?

[tonywatsonhelp]

Hi everyone,

I'm trying to create a Macro that can help me with my monthly and yearly budgets buy adding in the figures for me,

Heres what I'm trying to do.

I have lots of small loans from friends and family that now business is going well I want to start paying back
I also have my monthly yearly out goings that I would like to include so I get a total outgoings each month for the next 5 years.
What I'm hoping is excel can do the hard work for me and let me just add the figures once into the sheet rather then having to each one up to 60 times!

So heres what I want?

In columns A I have the Name of the Expence.
In column B The month it starts (this is very important as some bills don't kick in for 6 months like rent etc.)

Oh and although I might say the month it starts all dates are input as the first of the month so if it start in june the date will say 01/06/2016 (dd/mm/yyyy)

D is how often, this will only say one of two words "Monthly" "Yearly"
Durration so if it says monthly it might say 60, 36 etc, yearly, 2,4,5 etc.
E Amount,
F Full amount or payment, (This is important as most bill are a regular amount each month, say £20 so this would be maked as "Monthly" however others are the full amount I owe, for example Mums Loan £500, so if I've said its monthly over 36 mounths I need 500 divided into 36, if it yearly £500 divided into 3 ,

(you can see why I need help with this!!!)

Next

From column H I have a monthly calender again the date showing as 01/06/2016, etc.
for the next 5 years,

what I want is a macro that can look at each row,
look at the start date, if it says due monthly, workout the monthly amount and insert that amount into each month for the duration of the term, if its yearly once a year,

can anyone help me with this please?

If its easier I could split the data into,
Monthly with monthly amounts

Yearly with yearly amounts,

Monthly with full amount

Yearly with full amount?

thanks

Tony

 

[wideboydixon]

Hi Tony,

It shouldn't be too difficult but I think I'd really need to see some sample data. From your description I imagine something like this:

Book1
	A	B	C	D	E	F
1	Name	Start	Frequency	Duration	Amount	Type
2	Mum's loan	Oct 16	1	12	£500	F
3	Rent	Sep 16	1	60	£20	P
4	Bank Loan	Aug 16	12	3	£1,000	F
5	Overhead	Aug 16	1	60	£50	P
Sheet1

Note that I changed the Frequency to be a number of months (1 or 12) as it would make the VBA a little easier and also allow new values of 3, 6 etc. if necessary. For the last column I was using F for full amount and P for payments. Does this look reasonable?

WBD

 

[tonywatsonhelp]

Hi WBD,
that's that's fine, happy to change it slightly if it makes it easier, your example is spoy on how it will look.

Thanks

Tony

 

[wideboydixon]

Using this format:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Name	Start	Frequency	Duration	Amount	Type		Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17
2	Mum's loan	Oct-16	1	12	£500	F				£41.67	£41.67	£41.67	£41.67	£41.67	£41.67	£41.67	£41.67	£41.67	£41.67	£41.67
3	Rent	Sep-16	1	60	£20	P			£20.00	£20.00	£20.00	£20.00	£20.00	£20.00	£20.00	£20.00	£20.00	£20.00	£20.00	£20.00
4	Bank Loan	Aug-16	12	3	£1,000	F		£333.33												£333.33
5	Overhead	Aug-16	1	60	£50	P		£50.00	£50.00	£50.00	£50.00	£50.00	£50.00	£50.00	£50.00	£50.00	£50.00	£50.00	£50.00	£50.00
Sheet1

I used the following code:

Public Sub CreatePaymentSchedule()

Dim lastRow As Long
Dim thisRow As Long
Dim thisCol As Long
Dim startDate As Date
Dim frequency As Long
Dim duration As Long
Dim amount As Currency
Dim paymentType As String
Dim firstMonth As Range
Dim paymentCount As Long

Set firstMonth = Range("H1")

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For thisRow = 2 To lastRow
    startDate = Cells(thisRow, 2).Value
    frequency = Cells(thisRow, 3).Value
    duration = Cells(thisRow, 4).Value
    amount = Cells(thisRow, 5).Value
    paymentType = Cells(thisRow, 6).Value
    thisCol = DateDiff("m", firstMonth.Value, startDate) + firstMonth.Column
    For paymentCount = 1 To duration
        Select Case paymentType
            Case "F"
                Cells(thisRow, thisCol).Value = amount / duration
            Case "P"
                Cells(thisRow, thisCol).Value = amount
        End Select
        Cells(thisRow, thisCol).NumberFormat = "£#,##0.00"
        thisCol = thisCol + frequency
    Next paymentCount
Next thisRow

End Sub

WBD

 

[tonywatsonhelp]

Thank you WBD,
this is exactly what I needed

Thank you very much

Tony