Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Coding a VBA loan repayment calculator. Help a noob :)

Hi forum,
So I have a bit of a problem. My programming skills are weak to say the least and Im trying to create a fancy spreadsheet in my computing class. The task is to create a sheet in which you enter the sales data (UserformHPI) and it then prints the info you enter onto a table. I have completed that part and can be seen if you click 'Enter sales data' on the data sheet. The problem I have now is selecting that data [one customers info] and calculating the Required down payment, monthly repayments, outstanding balance and due date of the first repayment for them based on the values in the VBA Userform (UserformHPC). Its hard to explain on here so i have uploaded the spreadsheet to my website for you to grab

http://cyberbeatnz.com/Excel/HPSales.xls.zip

The other thing I am finding difficult is the code to sort the data in the table into an order selected by an optionbox in a userform (Userformsort). Err I wish i could do it myself :P

If anyone is brilliant enough to help me out on this one it would be greatly appreciated!
Thanks heaps in advance for your help!

Nick


Post your answer or comment

comments powered by Disqus
Hi all,

Maybe someone out there can help. I have a loan facility for the period 1 Jan 08 to 1 Jan 18, but with variable draw-downs on the facility over the first few years.

So in the first year I may draw 20% of the loan, the second 25% and so on.

Is there a straightforward way of calculating the principal repayment amounts (assuming equal repayments of that principal over the remainder of the loan period) without having to treat each draw down as a separate loan?

It's late here and I think maybe I am missing something obvious!

Thanks!

Dear All,
Can someone help me with writing a VBA code for the Dsum function. I basically want to have a VBA formula which can help me write the criteria in the VBA formula itself rather than as reference to another range as it is currently in the Dsum function. I have attached an excel file where I am showing the Dsum function, however, I want to do the same thing, but using a VBA code. The reason is that I have a huge amount of data and I want to avoid writing the Criteria in a separate range of cells and then refer to the range in the Dsum function. Many thanks for your help.

As in Visual Studi one builds his/her code using Build- how to build your program/code in VBA? Can anyone please help?

Hi

I need a VBA code to do the following.Would appreciate your help in this regard
For every Bill to party in column A(the bill to party number is unique)

1.In column “Count(Sales Doc)”, sum the number of 1s and display the sum in the results row
2. In column “Count(Diff)”, sum the number of 0s and display the total number of 0s in the “results “ row(the yellow row)
1.In Column J under “ Percentage” I want to use the following formula and display it only for the results row:
Count(Diff)
---------------- x 100
Count(Sales Doc)
Hope the above is clear .Let me know if its not.

Thanks for the help in advance

Raja

Hi,
I was wondering if anyone can help in what formulas to use to calculate home loan repayments. I have used the PMT function but this gives me the total to pay per month.
I want to know what the repayments would be and also allow me to add in each one of the periods and extra repayment option.
So if in the year I wanted to pay an extra $100 per month, I would put $100 next to each period as in a particular period(s) I might not have to put in there but want to predict what the amount owing on the house is.
Is this possible or is this too complicated.
Regards,
George

I need help to write part of a VBA code in Excel. I am trying to do
calculations on rows that meeting certain requirements, but I do not
know how to established that in VBA. I know that if I use the
worksheet itself, I can do something like SUMIF() function. But, when
I put the same function in the VBA editor, it returns error. A simple
version of my data:

Group No Member No Amount spent
1 1 10
1 2 3
1 3 4
2 1 6
2 2 1
3 1 12
4 1 3
4 2 1

I am trying to calculate the total amount spent based on the group no.
FOr example, how much does group no.1 spent, how much does group no.2
spent, etc.

I appreciate anybody who can help me with this. Thank you very much.

Hi I need a help on a VBA code. Suppose I have huge data in which I want to retrive or extract data as per the country name. For e.g. I have "Canada" & "USA" in my list, now what I want that my VBA macro should be able to get the data from the list according to the country i.e. only data from "USA". Please help me out. I have stuck.

Hi folks. I've created a spreadsheet that is used to calculate loan repayments.

It's based on an online calculator, and the figures in the spreadsheet have to match the online results. The trouble is I can't match it exactly and need help with this.

The calculator works out 2 things;

1. What the loan balance will be after 25 years (300 months) using a fixed monthly payment.

2. What the loan balance will be after 25 years (300 month) using an increasing payment based on an increasing salary.

I'll try and explain what it does at the moment;

The inputs are Salary (A), Loan amount (B), Annual Interest Rate (C), Salary Increase rate (D), Fixed Monthly Payment (E), Increasing Monthly Payment (F1-F25) i.e. one for each year.

I can work out what the fixed monthly repayment amount is based on the salary. And what the increasing monthly payment will be based on an increasing salary. The formula to work out the increasing salary is A*(1+D)^1, and then from that I work out the monthly payment. This part works ok.

The part that I can't get to match is the decreasing Loan balance e.g. what the balance would be after deducting the monthly payment, and adding the interest for that month.

The formula I'm using is;

(B-E)+((B-E)*(C/12))

This is worked out for each month, and the Loan amount for the 2nd month is the balance at the end of the previous month, and so on.

For increasing payments, the Formula would be;

(B-F1)+((B-F1)*(C/12)) for all of year 1, then F would increase for year 2, 3 and so on.

I am doing this right, is there a more accurate way? Or is there a way I can improve this method to get it to match what's getting done online.

The formulas in the spreadsheet are the same as what the code of the online calculator do.

thanks

Does anyone know how to write code in VBA that will calculate the frequency of strings from a list?

the kicker:
-there is no predefined number of strings
-there is no predefined list of strings to reference

so each time a new data set is entered the total number of entries may change, and the order and composition of the list of strings may change.

I appreciate any help that you may be able to give.

Thanks!

Hi

I am an absolute novice with Excel (only used templates, mostly...) so please excuse if there is a simple answer to my issue which I have overlooked.

I am borrowing $2500 from a friend. I will be repaying different amounts at different times. We have agreed that I will repay the $2500 plus the interest she would have earned from her savings account on the amount loaned, 1.5% annually (which may change if rates improve before I have fully repaid her) calculated daily and paid monthly.

She is loaning me the money to help me purchase equipment needed to start a small business (a new camera for my photography business), I will be paying her back with any profit I earn until I have repaid the amount including the interest.

I am not sure if I am overthinking the situation and all it is is a simple interest calculation or if this is a more complicated issue. I am looking for a way to be able to input the amounts I have repaid and calculate the interest on the amount still outstanding until I reach a zero balance owed.

I think it's simply a FV calculation in which I would change the PV each time I make a payment, but I am not sure and would love some experienced feedback.

Any help or guidance in figuring this out is MUCH MUCH appreciated!!!

Hi everyone. I'm sure that some clever sparks here will be able to enlighten me on using a formula for the function I want.

I want to calculate the amount of money I will pay over a certain value.
i.e. Everything over £15000 of my salary will be charged 9% towards student loan repayments.

I was thinking (for cell N3): =IF (D3>15000=D3*0.9) (but this doesnt work)
"D3" is the cell which has my salary in.

I'd really appreciate any help!!

Thank You!

Hello,

I need a vba code that will work in the following way.

I have 2 sheets in a workbook Sheet("Movement Box") and sheet("B1 Movements").

In sheet ("Movement Box"), data starts from the 1st row. Column "C" has reference numbers for example "44332211" which are unique. Column "S" has a names filled in certain rows for example "Paul" and the remaining cells in column "S" are mostly blank cells.

In Sheet ("B1 Movements"), data starts from the 3rd row. Column "C" has reference numbers from "C3" for example "44332211" which are unique.

I need the code to do the following:

In sheet ("Movement Box") loop down column "S" from "S3" until column "C" from "C3" has reference numbers, and only for each notBlank cell (meaning only if there is a name) lookup the respective reference number in column "C" in the same row, find the matching reference number in Sheet("B1 Movements") and update column "M" in Sheet(B1 Movements") in the respective row with the name.

Hope someone out there can help me out. Thanks in advance.

I would like to create a VBA function that calculates federal and state income taxes. I know how to do this in Excel using a table, but I do not want to put the calculator in an Excel spreadsheet. Instead I want the data (i.e., the tax tables) to be in the VBA macro that I can access as a function in any spreadsheet. I would think that I would need an array, and a loop of some kind. I'd like to be able to enter the tax table easily into the VBA macro. I think that the arguments of the tax rate table are taxable income, filing status and year. I've got to believe that this has been done before -- I did it many years ago in BASIC but i forgot how to do it. I remember that I would type in a line that would be something like (7,825,31,850,.15,782.50) and the program would look at the taxable income. If it were between (in this example) $7,825 and $31,850 it would take the taxable income, subtract $7,825 from it, multiply that by .15 and add 782.50. That would be the function's result. If the taxable income were greater than $31,850, it would go to the next line, which would also be four numbers representing the parameters of the next tax bracket. Do you know where either I could find sample code that I could adapt, or could you provide more guidance on how I write the code myself? Thanks.

Hello all,
I have just started coding in VBA but just got stuck.
I am writing a module which have a different test based on for each of the previous 2 column cells.
It calculate the days passed or in simple way calculate the date difference for the 2 columns and puts them in third. But i nned to use a inputbox for entering the first date for monday of the month each time i use the worksheet.
When i try using the module for each cell of the column it display the input box for each cell.
is there any way so that i just enter the value in input box and it can be used in rest of the module.Without using it again and again

Please help.

Thanks
Ken

.
.
Hello Masters,

I have a VBA code that creates new sheets.

The code:
* Sorts columns niv1 and niv2 of Sheet "consolidado".
* Using an Advanced Filter, it creates new sheets based on column niv1 of Sheet "consolidado".
* Copy in each new sheet the registers of Sheets "consolidado", "informacion" and "indice".

Now, here starts my problem.
I need the code does the same operation with column niv2, and next with column niv3 of Sheet "consolidado".
In short, I need to create new sheets for C.1, C.2, N.1,.......etc., of column niv2; and for U.2.1, U.2.2, .....etc., of column niv3 of Sheet "consolidado".

If columns niv4, niv5,...., niv10 would have data, the code should create new sheets for those columns too.

Please Masters, help me to include to the VBA code the above explained.

I enclose my file.

I will highly appreciate your kind assistance.

kindest and best regards,
Pedro
.
.

Hi All

I have a £4K loan for 7 months, the repayment I need to make is 2% of the outstanding balance can anyone think of a good formula to calculate the total payment to be made in 7 months.

The best I have come up with is:

=(4000*0.02)+((4000-(4000*0.02))*0.02)+4000-(4000-((4000-((4000-(4000*0.02))*0.02))-(4000*0.02))*0.02) ect

that is just for 3 months so you can see it would get very big very quickly also not very quick to work out either.

Thanks Danny

Hmmm - Just tried it on XL2000 and it worked fine. You haven't got any event
macros that might be conflicting as it was a new workbook.

Do other macros work OK, eg just doing basic stuff. Is this peculiar to just
Solver?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Wayne Stewart" > wrote in message
...
> Thanks for the response Ken.
>
> I just loaded up a new excel workbook and inserted your
> ExampleSolv macro in a module within this new workbook. I
> established the Solver reference as instructed since the
> module is new (this was done already on my original macro).
>
> When I run the macro nothing happens (just like my
> original macro). The screen seems to blink for a
> microsecond, as if something is being calculated in the
> background. However, cell A2 doesn't change from 5 to 1
> like it's supposed to.
>
> This is a very basic macro test and solver doesn't
> respond. Is the problem with Excel?
>
> For you reference, I am running Windows 2000, Excel 2000
> SP-3
>
> Any other thoughts?
>
> Thanks.
> -Wayne
>
> >-----Original Message-----
> >Do you mean you still have to hit OK on the dialog box
> for it to take the value?
> >Have you referenced the Solver addin from your project
> (tools / References /
> >Check 'Solver')
> >
> >Does your code look something like this:-
> >
> >Sub ExampleSolv()
> > SolverOk SetCell:="$A$3", MaxMinVal:=3,
> ValueOf:="10", ByChange:="$A$2"
> > SolverSolve (True)
> >End Sub
> >
> >Note you need the parameter True set on the SolverSolve
> bit if you want it to
> >accept the value within the code without a dialog box.
> >
> >Does the above code work for you if you try that with say
> 10 in A1, 5 in A2 and
> >=A1*A2 in A3?
> >
> >--
> >Regards
> > Ken....................... Microsoft MVP -
> Excel
> > Sys Spec - Win XP Pro / XL 00/02/03
> >
> >----------------------------------------------------------
> ------------------
> >It's easier to beg forgiveness than ask permission :-)
> >----------------------------------------------------------
> ------------------
> >
> >
> >
> >"Wayne Stewart" > wrote in message
> ...
> >> I am trying to automate the Solver routine (Under the
> >> Tools pull-down menu) using a VBA macro. I have
> succeeded
> >> at setting the constraints and the various options using
> >> the "SolverAdd" and "SolverOptions" commands. However,
> >> the problem seems to be with the "SolverOK" command,
> where
> >> the "Set Target Cell", "MaxMinValue", and "By Changing
> >> Cells" fields are set. Solver does not seem to register
> >> these values when the "SolveOK" command is used. Also,
> >> when I use the "SolverReset" command followed
> >> by "SolverAdd", and "SolverOptions" commands, no
> >> parameters are passed to solver either.
> >>
> >> The only way I can get the "By Changing Cells" cells
> >> actually changing on the excel worksheet is to first
> >> invoke solver manually (outside the macro), get the
> >> solution by clicking "solve", then deleting the fields
> in
> >> the solver dialog box, closing solver, and then running
> >> the macro!
> >>
> >> Is there a glitch with Excel? Is there a patch that can
> >> be downloaded to fix this problem? I'm positive that my
> >> syntax is correct (the help examples are very straight
> >> forward).
> >>
> >> I don't get any error messages when I run the macro. It
> >> simply just doesn't present the solution on the
> worksheet.
> >>
> >> Hope someone can help me!
> >
> >
> >---
> >Outgoing mail is certified Virus Free.
> >Checked by AVG anti-virus system (http://www.grisoft.com).
> >Version: 6.0.567 / Virus Database: 358 - Release Date:
> 24/01/2004
> >
> >
> >.
> >

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.567 / Virus Database: 358 - Release Date: 24/01/2004

Hi

I am a vba self taught hack, and nota very good one at that, and i need some help with variable length arrays. Below is the code I have which works perfectly but does not use arrays.

I have 4 columns. In Column A is a list of names. There are always atleast 2 or more of the same name and the same names are grouped together one under each other.
Then in Column B is a list of numbers representing qty of an item sold by that person. Then in column C is the time in number of days that it took that person to sell that qty.
Nowhere in the VBA code do i refer to column C, but Columd D is Column C multiplied by 5.
This function allows me enter a persons name and a random number of days that does not neccessarily correspond to any exact number of days related to that persons sales up to that period allowing for interpolation and extrapoltion, spewing out an interpolated or extrapolated number
Below is the code I have which works perfectly.

Function QtySold(PersonName, Days)
'First I Find the row number for start of Range in column
NameRange = Range("A1:A5000")
Startrow = Application.Match(PersonName, NameRange, 0)

'now I find the rownumber for end of range in column
    Endrow = Startrow + 1
    TestforName = Cells(Endrow, 1)
    While TestforName = PersonName
        Endrow = Endrow + 1
        TestforName = Cells(Endrow, 1)
    Wend
Endrow = Endrow - 1

Set TimeRange = Range("C" & Startrow & ":C" & Endrow)
Set CalcRange = Range("D" & Startrow & ":D" & Endrow)
nn = Endrow - Startrow + 1 'number of variables in range

QtySold = PLI(TimeRange, CalcRange, Days, nn)

End Function
This function above calls on the following Interpolation extrapolation function.

Function PLI(Xvalues,
Yvalues, x, n)
    xPos = Application.Match(x, Xvalues, 1)
    If Application.IsNA(xPos) Then
        i1 = 1
        i2 = 2
    ElseIf xPos = n Then
        i1 = n - 1
        i2 = n
    Else
        i1 = xPos
        i2 = xPos + 1
    End If
    x1 = Xvalues(i1)
    x2 = Xvalues(i2)
    y1 = Yvalues(i1)
    y2 = Yvalues(i2)
    PLI = y1 + (x - x1) / (x2 - x1) * (y2 - y1)
End Function
The above two functions work perfectly, however CalcRange (Column D) is a function of column C multiplied by 5 and what I want to dois create an array of numbers called CalcRange in VBA and use that array when my function calls on the Interpolation function ..QtySold = PLI(TimeRange, CalcRange, Days, nn) and not have to calculate it seperately in excel like i have done in Columd D.

Below attempt is a disgrace i know...but if anyone can help me it would be much appreciated

Function QtySoldAttempt(PersonName, Days)
'First I Find the row number for start of Range in column
Dim CalcRange As String
NameRange = Range("A1:A5000")
Startrow = Application.Match(PersonName, NameRange, 0)

'now I find the rownumber for end of range in column
    Endrow = Startrow + 1
    TestforName = Cells(Endrow, 1)
    While TestforName = PersonName
        Endrow = Endrow + 1
        TestforName = Cells(Endrow, 1)
    Wend
Endrow = Endrow - 1

Set TimeRange = Range("C" & Startrow & ":C" & Endrow)
 
nn = Endrow - Startrow + 1 'calcualtes the number of variables in the array

'now instead of setting the Calcrange to column D as before, i want to create an array with
'the numbers of the column C numbers multiplied by 5
For icount = 1 To nn
rnum = Startrow
CalcRange(icount) = Cells(rnum, 2) * 5
Next icount

QtySoldAttempt = PLI(TimeRange, CalcRange, Days, nn)

End Function
I hope i have explaind my request. If it is not understood it is no doubt my bad explanation.
Thanks to all those people in adavnce who look to help me.

Hi can any one help me out to find a VBA Code for fliping images in a User Form. What I want is that, I have a user form in which I have a command button "Next" & "Previous". I want whenever I click "Next" button my image control should show the next image & whenever I click "Previous" button my image control should show the previous image. I tried many logics but failed. Might be you guys will crack this hard nut. Please post your comments if you need any further details from my side.I will appreciate your help.

I have 9 charts, and to select & show one at a time, I used an image and assigned a formula to it, using a named range " getChart" and a Combo Box. This method works OK except that it is causing Excel to be so annoyingly sluggish.
To overcome this sluggishness, I need a VBA code which sets the formula for the image ( forcing the chart to change) and deletes the formula from the image again to leave it ' static'.
I've noticed that when I manually delete the formula, the selected chart stays and Excel sluggishness goes away completely, but without the formula no other chart can be selected. A sample workbook is attached
Any help will be greatly appreciated.

Could someone help me transform this formula into a VBA code? I am not very good at coding any formulas so any help would be appreciated.

This formula is in cell L14 and i will need to duplicate it accross cells in the range of L8: Q44, but i'm figuring i might be able to copy and paste after i get it in a VBA format and just change my values.

=IF(ISNA(INDEX('Weekly Total'!$B$8:$H$2000,MATCH(1,('Weekly Total'!$D$8:$D$2000=$D$14)*('Weekly Total'!$B$8:$B$2000=L7),0),7)),"",INDEX('Weekly Total'!$B$8:$H$2000,MATCH(1,('Weekly Total'!$D$8:$D$2000=$D$14)*('Weekly Total'!$B$8:$B$2000=L7),0),7))

I need help.

I have this formula:

And I tried using this code to run instead of the formula above because the formula takes about 10 minute to run for
worksheets, but it gives me #Value! at row 100.

Sub Run_Data()
Dim iLastRow As Long
Dim i As Long

    With Sheets("Info1")
        iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        For i = 3 To iLastRow 'iLastRow to 1 Step -2
            .Cells(i, "K").Value = Evaluate("=IF(ISERROR(VLOOKUP(IF(C" & i &
"="""",VLOOKUP(" & _
                "D" & i & ",Data!B:O,F3+2,0),IF(D" & i &
"="""",VLOOKUP(" & "C" & i &
",Data!B:O,F3+2,0))),$O$3:$O$114,1,0)),H" & i & "& ""C0MISCELLANEOUS"",H"
& i & _
                "&(VLOOKUP(IF(C" & i & "="""",VLOOKUP(D" & i &
",Data!B:O,F3+2,0),IF(" & "D" & i & "="""",VLOOKUP(C" & i
& ",Data!B:O,F3+2,0))),$O$3:$O$114,1,0)))")
        Next i
    End With
End Sub

And how would I write a VBA Code to run from F7 to DN71 for the formula below:

I could have used a code similar to the code for the vlookup one above, but I'd have to do that code for too many column. I
want a more flexible formula.

I cross-posted or posted-multi posted because the code didn't give me the desired result and I needed a response quickly.
And I think I got into a little bit of trouble. Well, that's what I get for not reading the rules.
Thanks
Xrull
http://groups.google.com/group/micro...&q=dingo&pli=1
http://www.mrexcel.com/forum/showthr...ighlight=xrull
http://www.microsoft.com/office/comm...a-bced830f0d47
Thanks,
Xrull

Hi. Can someone pls help me? I have been searching for a way to keep track of a loan due to me. The interest has been constant however the borrower has missed payments, made late ones and partial ones. The excel loan analysis workbook/sheet does not properly calculate the missed or late payments. Other than that the format/layout is perfect. Do I need to create a new workbook or can I modify the loan analysis worksheet, and if so HOW??
I am VERY new to Excel and not familar with a whole lot yet.
Any help and patience is very much appreciated.
Thanks for your time!!
Lisae

Hi

I am trying to write a VBA macro that will mimic what i have already done in an excel worksheet i have made. The excel worksheet uses inputs for a heat exchanger and finds the temperature as it leaves the heat exchanger. This temperature is then the new input for the second line of the worksheet and it changes quite a few of the values in the sheet. I have the excel worksheet working where you just drage the row down and it does it automaticly, but i would like to have a VBA macro that all the values can be input in with the number of cycles until the heat exchanger equalizes out.

I am able to get the macro to work when it calculates just the first cycle, all the equations work my variables are ok for just one time. What i don't know how to do is make my program realize that it has to use the calculated temperature out of the heat exchanger as my new input for the heat exchanger. I know how to do it once so it is more of a syntax or understanding of a loop or an array for the values that are changing.

Thanks any help is appreciated.


No luck finding an answer? You could always try Google.