Loan Formular To Capture Overdraft Bank Facility
how I can solve the issue of creating a spreadsheet (similar to an amortization one) that could deal with unequal re-payment regime as well as unfixed (anytime of the month) payment periods.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Game Points Bank Account (running Total For The Bank)
Sheet6 RSTUVWX5Your PointsPoints Needed To Level UpCurrent LevelYour CreditWithdrawalDepositCredit67 80You Need 18,000 Pts.IntermediateYour Credit is 0 Pts 911,5006,500BeginnerYour Credit is 0 Pts25004000 1010,4547,546BeginnerYour Credit is 454 Pts 45411125005,500BeginnerYour Credit is 1000 Pts200035001000 Spreadsheet FormulasCellFormulaS8=IF($AB$6$Q$23,"You Are Credited With "&TEXT(X8-$Q$23,"#,##0")&" Pts","Your Credit is 0 Pts") Excel tables to the web >> Excel Jeanie HTML 4 i have a "Game Points Bank Account" that keeps track of the points users earn or spend in the game...what i need help is creating a running total for the bank..ive tried everything nothing works.. (this is actually like a traditional bank account) each player starts with 10,000 points *Row 8 is the only row to have formulas* *Rows 9-11 are typed in to show my desired results* Here is the point value "Ranking System" for Column T Sheet6 AGAH5Tier 16Beginner180007Beginner360008Beginner540009Intermediate7200010Intermediate9000011Intermediate10800012 13Tier 214Advanced12600015Advanced14400016Advanced16200017Expert18000018Expert19800019Expert21600020 21Tier 322Elite23400023Elite25200024Elite27000025Superior28800026Superior30600027Superior325000 Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
Formular For Pythagoras
how do i write the formular of pythagoras, I input a1^+a2^=c^ excel informs of an error I have also tried a1+a2^'c still errors can any body give me advice to what would seem such a simple input or am i misssining something here
View Replies!
View Related
Disable Print Facility
I have a spreadsheet with a print button which allows the user to print only the parts I want them to see. I have removed the Printer icon on the toolbar, but does anyone know how to remove the Print option on the File menu? (File/Print)
View Replies!
View Related
On Click Method To Open The Find Facility
I was wondering if possible i could use an on_click method to open the find facility in excel. I know it may be easier to use the Edit, Find or Ctrl+F way. But i have people using excel with no past experience what so ever, and i would like to make it as easy as possible....i.e a click of a button that says "Search".
View Replies!
View Related
Loan Balance
I owe 15462 in the bank, currency dont matter here, that is what I owe right now, but I want to have a cell in the frontpage with the amount left, so can I make a line called =remaining-each month the amount should then each month be substracted from the new month and so on, until the amount is 0 can this be done? the second page in the spreadsheet has a post with monthly pays to the bank ...
View Replies!
View Related
Bank Interest Calculator
I'm an amateur to macro as I'm only in a low level class at a university... But I'm attempting to make a macro for a bank interest calculator. It asks your type of account(which then assigns an interest rate to it), how much money is in the account, and also how long th emoney will be in the account. I used a "Select Case" for the account types, but I seem to be struggling for it to work, it won't put the value of the total into the assigned cell, or it's just not computing it(as I get "0" each time I run it)... Public Function BankCalculator() 'Bank Calculator for different accounts 'declare variable Dim shtBank As Workbook, strAct As String, intMon As Integer, strLong As Integer, intTotal As Double Set shtBank = Application.Workbooks("Bank Calculator.xls") 'input box for amount of money, assign address intMon = InputBox(prompt:="How much money do you currently have in the account?", _ ......................................
View Replies!
View Related
UK Bank Holiday Calculator
I found a link to a website on one of the forum pages. I had a look in the website and it showed a formula for calculating when Easter falls - I didn't know it could be worked out, but it can!! I therefore decided to investigate further. I picked up another formula to calculate the first MOnday in May and I have now put together a little spreadsheet that will calculate all bank holidays in the year entered in cell B1. It also takes into account additional bank holidays that exist when Christmas Day and/or Boxing Day fall on a weekend.
View Replies!
View Related
Insert Bank Rows After Each Occurences
I want to insert two blank after each broker. I am copying each broker's information to another spreadsheet. each broker information may take several rows...after copy one broker information, I want to automatically insert two blank rows...how can I do that in VBA excel?
View Replies!
View Related
Loan Amortization Calculations
I'm working with a loan amortization worksheet (downloaded from Office Online). Unless I don't understand correctly, the date formula on the worksheet doesn't calculate the way I need it to. I'm not totally sure what the formula they use is doing. It does use a lot of named ranges. If a user inserts the total "Number of Payments per year", then I want the date to return the proper payment date. For instance, If the start date is 1/1/07 and the number of payments per year is 24 then the payment dates should be like 1/15/07 1/31/01 2/15/07 2/28/07 It should be the 15th and the last day of the month. If I put 52 as the number of payments then I want the formula to set the payment dates to every Friday. I'm still learning formulas so bare with me guys. Attached is the worksheet.
View Replies!
View Related
Multiple Loan Repayment Optimization
I have 4 Loans of various interest rates, balances, and minimum payments. Assuming I have a certain amount of money to pay out each month, how can I minimize the total amount I pay over the lifetime of the loans? Given: Total Monthly payment: M Interest Rate for each loan: R1, R2, R3, R4 Initial Principal for each Loan: P1, P2, P3, P4 Minimum Monthly Payment: Min1, Min2, Min3, Min4 Each month, how should I distribute M over the 4 loans?
View Replies!
View Related
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.
View Replies!
View Related
Is Loan Amortization Affected By The Currency
There are various references and links to " mortgage calculators;" though they are specific to the US dollar. Is the formula still the same, irrespective of the currency and why does it come across as quite a complex calculation? i have been taksed with designing a "calculator" and don't seem to know where to start as the currency issue is confusing me.
View Replies!
View Related
Find Specific Withdraw Entries From Bank Statements
I use online banking for paying my monthly bills. I get different confirmartion for each pay transection. Pls look attached Excel file. Now I want to find specific bills expense with amount from all my 12 months bank statements. How can I get this task done. Note: Each time get different confirmation which appears on my bank statement beside paid bill name. for example January 2009 statement GAS Z8A.... Feb 2009 GAS S2W. I hope this clarify. What I have to do if I want to get all my 12 months GAS money withdraw from bank statement. I use csv file for bank statement.
View Replies!
View Related
Effective Rate Calculation, Interest Only Loan
I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas? Here is the effective rate calcs on a random normal amortization loan: this is in B2, and answer is 7% =RATE(B4*B5,-((B3+B7)/B6),B7)*12 B3 = Total*Interest 279017.8 B4 = #*Years*in*Loan 30 B5 = #*Payments*/*Year 12 B6 = Total*Payments 360 B7 = Beginning*Principal 200000 B8 - Ending*Balance 0 problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan. Here is the example I'm working on... A client's loan is the following: Loan amount - 131,538 interest rate - 6.15 30 year amortization 10 years interest only normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about $178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan.
View Replies!
View Related
Calculate The Monthly Repayments On A Loan Taken Out Over A Certain Amount Of Years
I need to calculate the monthly repayments on a loan taken out over a certain amount of years, which I can do fine. I just cant get my head around how to calculate monthly repayments over a certain amount of years when the intrest is compounding annualy. What I have so far: p*(1+(r/100))^n Where p is value of original loan, r is annual intrest rate, n is amount of years, and I am hoping I am right in saying this is the total repayable amount of the loan? Then putting that aside I created a amortization table. (which I am certain i forgot to include compound intrest in!) To keep it short i followed this guide for the amortization table. and now I am so confused about if I should be using PMT, PPMT, NPER?!
View Replies!
View Related
Automatic Finish Date & Time With Lunch Break And Bank Holidays
I would like a solution for the automatic calculation of the end date and end time for project tasks. I have already spent hours on the issue, thanks for any help on this. The parameters are: A1 = Start time 08:00 B1 = End Time 17:00 A2 = break lunch 12:00 B2 = back from lunch 13:00 Task parameters A5 = start date 01/03/10 (entered manually) B5 = start time 10:00 (entered manually) C5 = duration 02:00 (hrs entered manually) D5 = "end date" >>> (to be calculated exluding breaks and holidays) E5 = "end time" >>> (to be calculated exluding breaks and holidays) the next line should be filled in automaitically according to the hours needed and the previous end date & time A6 = "start date" >>> (after line 5: to be calculated exluding breaks and holidays) B6 = "start time" (after line 5: to be calculated exluding breaks and holidays) C6 = duration 14:00 (entered manually) D6 = "end date" >>> (to be calculated exluding breaks and holidays) E6 = "end time" >>> (to be calculated exluding breaks and holidays) and so on for every new line down.
View Replies!
View Related
Sheet Has Lost The 'UNDO' Facility
I have created a spreadsheet in the field of marine surveying. In that sheet, I have used a lot of macros (21 modules) and a lot of VBA, most of it provided by kind people on this forum. But what has happened now, is that when I use that particular worksheet, the 'Undo' menu item in the 'Edit' menu has become permanently grayed out and it says 'Can't Undo'. In other words, I cannot undo, even if I have made the wrong entry in an unprotected cell. If I use any other sheet, or make a new sheet, everything returns to normal. So, is there any way I can re-gain the 'Undo' facility, while using that particular sheet. I am using Excel 2003 on a Windows XP Home (SP3) platform.
View Replies!
View Related
Bank Reconciliation - Many To One Matching And Partial Cell Matching
programming 2 macros in a bank reconciliation sheet I am trying to create. Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount. The following two situations can occur and which need to be covered with a macro. Many to one matching When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled. Partial cell matching In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
View Replies!
View Related
Loan Processing-Copy Range From 1 Sheet To Same Range On Another
I'm developing a loan processing system for members of a club. When an applicant asks for a loan, the club will calculate 10 % of that interest and the applicant will have to pay it back in 5 successive fortnightly instalments. If he asks for a loan in the first fortnight (1), for example, he will have to start paying instalments in fortnights 2,3,4,5,6 to pay it all back. The system currently has 4 worksheets. The first sheet is a the loan application form. The cells outlined in thicker border, are the cells in which details must be input. Once it is input, the data will be automatically placed in the Processing worksheet using IF and VLookup functions (See spreadsheet attached), which is used as a basis for the loan schedule Worksheet. What I need is a macro that will copy the range filled in the Processing worksheet, and copy it to the exact same location in the Loan schedule worksheet (The cells with the same fortnight columns and the same member name. This is how the loans are to be filed.
View Replies!
View Related
Capture Current Time
I am preparing a template on the con-call done with various states. What I want to do is to capture their log in time to the call. 1) As soon as participants log in, we need to input the time of that moment. Click or double click with the cell should give me the current time. Then; 2) Need to calculate how many participants, logged in to the call On-time by considering the log in time captured. 3) Similarly, we need to count the participants joined within 10 minutes of call and who are late comers.
View Replies!
View Related
Capture Some Characters Within The Cell
i have a long text in A1 field and i just need to capture some characters in between. Below is the example. Raw Data: Cell A1 (r1,c1) = Target: ABC, CustomerOrder, Results: BDE, LastUpdate: 12Dec08 I want to get the Result as below: Cell A2 = Target: ABC Cell B2 = Results: BDE Can this be done in Excel Cell format? Or do i need to do it in MS Access?
View Replies!
View Related
RefEdit To Capture Range For Other WB
I have a userform with a 3 refedit controls. Each needs to capture a range from a different workbook (already open - only not active). Is it possible to configure the refedit to capture a range from another workbook (either thru window menu or ALT+TAB)? I notice that InputBox type:=8 allows it - I'm a little suprised that default refedit doesn't.
View Replies!
View Related
Capture A Target Number
I have a number result in a cell (d1) from a calcuting formula elswhere on the spreadsheet. As the worksheet is constantly calculating the result in d1 keeps increasing and decreasing I would like a formula in cell f3 that will give the result 1 if the d1 has reached the number 45. If cell d1 has not had the number 45 appear f3 will be 0
View Replies!
View Related
How To Capture The Value From A Running Live Value
I have a cell (for example, "A1") which is inserted with a WINROS formula to retrieve some data into my spreadsheet. And it is running live at all time. Actually, I can't think of any formulas to capture the value from cell "A1" to "B1". Because I do not want the value that I captured into cell "B1" running live. I just want the value "B1" fixed after captured.
View Replies!
View Related
Find And Capture Data
I need to find a term "aa" and upon finding this term, the term on the right must be "bb" before we capture the number. Output will be 15 for this case. Note that we do not know which cell "aa" will occur in, but the term "bb" will always be on the right of "aa". Can anybody help me with this? I have attached an example for your reference. Hopfully by modifying the below code, i can get the value i desired. Sub test2() Dim xx As Variant Set rng = Cells.Find(What:="aa" , After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ True) If Not rng Is Nothing Then xx = rng.Offset(0, 3).Value MsgBox xx If rng Is Nothing Then xx = 0 MsgBox xx End Sub
View Replies!
View Related
Formula To Capture Data In Cells That Are After It
I have a moving average spreadsheet that I am having trouble with. In column F of 'Data for Pivot' Tab, I have a set of data that doesn't begin populating until 101 data points (cell C8 in 'Mov_Avg_Chart' is the input; in this cell F138 of 'Data for Pivot' tab) after data begins populating in Column E of 'Data for Pivot' Tab (in this case data begins populating in Column E in row 38). What I would like to do is have the data in cells F38 to F137 = the value in cell F138. From there on forward (cells F138 and onwards), I would like the formula to calculate data in the same way as it is currently calculating it. That is, I would like the values in cells F38 to F137 be 0.00110 (in the current example). However, the data will be constantly changing. Thus, sometimes the data in column F will begin at a different cell than cell F138. In essence, I want the formula: when column E begins populating data but column F has not, I want those column F values to equal the value of the first populated value in column F (which is usually numerous cells below this value). I just can't seem to get this one right after spending considerable time on it. I would appreciate any help you can give on this.
View Replies!
View Related
Formula To Capture Values From One Sheet To Another
I have a spreadsheet that has two tabs that show different views of my report. In tab "Team View" I'd like to capture the information that is in "Cost Centre View" in the fields Nov09 to Oct10. I want my formula in "Team View" tab to take the TOTAL "Run Rate Reduction" value in "Cost Centre View" ONLY and have the same "Initiative Type" in "Cost Centre View" that align to what is shown in "Team View" in column A (i.e. ISO). I tried using a SUMPRODUCT but my formula didn't work. Is there another formula that will give me this calculation?
View Replies!
View Related
Formaula To Capture All The Dates Within That Month
=SUMPRODUCT(--('RD Import'!A3:A65000=A30),--('RD Import'!H3:H65000"RD"),--(MONTH('RD Import'!C3:C65000)=$C$25)) This is what I have and its very close. The only problem is the Month part I have a drop down box in Cell C25 that i can select any month of the year. On sheet 'RD Import' i have a bunch of dates (ex Jan2, 2010) that will be within that month selected on the different sheet. I need this formaula to capture all the dates within that month.
View Replies!
View Related
Capture Cell Value Before Change Event
I am trying to capture the value of a cell before a change even. The attached Macro will report the value before the change but does not store it so that I can use it in another module. What I am trying to do is capture the value before the change and then look that value up in another worksheet (in the same workbook) so I can make the same change in the second workbook. The values will always be in Column B and will always be string characters. The code I am using for the change event is as follows: ....
View Replies!
View Related
Adjustment To VB Code To Capture A Range
The code below puts a green border around the cell that is beneath 10 in my chosen range, however I wish to add the border to the row of information instead of just the cell. My columns of data are from columns E to M, but the criteria for whether or not the data gets a green border is in column D....so lets say D15 is less than 10, I would want a border to go around E15:M15. Sub Test() For Each c In Range("D2:D350") If c < 10 Then c.select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 4 End With
View Replies!
View Related
Capture The Averages (of Cells With Values)
I have a workbook with several sheets , the 1st 31 being named Mar.01-07, Mar.02-07,etc I'd like to capture the averages(of cells with values) from cells B7:C24 of sheets Mar.01-07 thru Mar.31-07 and place the values in the sheet called "averages" with the average for B7 going to cell B7 in the "average " sheet, B8's average going to cell B8 in the "average" sheet and so on.
View Replies!
View Related
How To Capture A Moving Target Total
I need capture a cumulative estimate as it moves from left to right across the page with each passing month. The estimate is pre-filled in for all the months. My task is to select from October to the current month and sum. Is there a way to do that in VBA?
View Replies!
View Related
SUMIF- Trying To Capture Some Summary Information
I have run into a problem trying to capture some summary information. Here is a brief description: I have the following fields: Game ID Team ID Player ID Goals I want to know, for each Game ID, how many goals were scored by the players within each team ID. A pivot table gets me close but I need this information in the following format: Game ID | Team ID | Goals. I am guessing there is some sort of sumif function that can get me close but I am struggling with the correct calculation. Here is a data set of 1 game (keep in mind there are 700 games, otherwise I would do this manually) Game|Team|Player|Goals 1|1|1|0 1|1|2|0 1|1|3|1 1|1|4|0 1|2|5|0 1|2|6|0 1|2|7|0 1|2|8|0 I would like to know that team 1 won game 1 by a score of 1-0 here.
View Replies!
View Related
Periodically Capture Cell Values
I would like to know how to capture a cell value at a specific date each month in a situation where this cell can change value as it is a formula. For example, the cell returns a percentage that is derived from other data that can change regularly. It may be 45% today, but a user may alter other data in the spreadsheet and it could be 67% tomorrow (or any other percentage). I want to graph a monthly trend of how this cell value changes by capturing the cells value on the 20th day of every month, or it would also be ok to work on a periodic snapshot of every 30 days.
View Replies!
View Related
Macro To Capture Who Accessed The File
Anyone know of a way to capture who (user name, machine name etc.) had the excel file open when a certain cell is selected? a Macro? Trying to use an Excel spreadsheet for a peer review where each reviewer accesses the Excel peer reivew file and enters the minutes they spent in the peer review but would like to also capture the user that had the file open when the minutes spent on the peer review were entered. This would provide proof that the actual peer reviewer opened the file and entered their minutes.
View Replies!
View Related
Capture Print Zoom Value Before Printing
In a previous, expired thread, the following code was suggested as a way to capture the page-setup zoom value after setting PagesTall and PagesWide. It works fine when single-stepping in the VB Editor and when run directly by the user, via a button click or Tools=>Macros...=>Run, but fails when run under a Worksheet_Activate() event call. Does anyone understand why that is and/or have a fix or workaround? Sub X() ' Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{1,#N/A})" Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{#N/A,#N/A})" MsgBox "Zoom factor is " & ActiveSheet.PageSetup.Zoom End Sub
View Replies!
View Related
Combobox Data Capture From Other Workbook
how data capturing works, using controls. I try to explain what I mean. I have a userform with a combobox that allows to select data from a worksheet in another workbook (database). Using VBA, I'm asked to define the "source" for the combobox. I don't understand the difference between controlsource and row source because when I close the userform, I find only the first field filled in (column "A"), while I would like the entire row to be copied.I also would like to have an "ok" button to allow me for multiple selection. In other words: I select the desired data from the combobox I click on the "ok" button ahd this event should copy the data (entire row) selected via the combobox on my worksheet without closing the userform in order to allow me to select other data on the same session. The row to copy data into is selected using the firstemptyrow method..
View Replies!
View Related
Capture First User Of A Shared Workbook?
Using Excel 2000, I have a shared workbook which is effectively a single sheet, flat database. Rows contain dates, and when certain dates occur, I want to send an email alert to specific users. I have searched and found lots of guidance on that aspect, but am wondering how best to trigger the email alert. At least one, and up to 10 users access the shared workbook at different times of day, and I only want to review the data and send the email alerts to the relevant users once per day. I thought about a timed event, but cannot be sure the shared workbook will be open. I wondered whether an event in Personal.xls might do it, if I ensure I open Excel every day, but it's not ideal. Finally, I wondered if I could somehow trigger the alerts when the first user opens the workbook - but can't find how to capture that.
View Replies!
View Related
Capture Sheet Delete Event
I wonder how to capture de event when one choose to delete a sheet. Similar to vbyes or vbno?? Now it inmediatley goes to my sheet_deactivate_event, but then I don't know if it is beiing deleted? I mean capture if you do righclick sheettab
View Replies!
View Related
Concatenate In Each Bank Cells, The Cells Above
I am searching for VB code to concatenate various length, and spaced rows of text, putting the end result in the blank cell above each group. For example (assume excel rows are as below): concatenate a2 thru a5 texta2 texta3 texta4 texta5 (blank) concatenate a8 thru a9 texta8 texta9 (blank) (blank) (blank) (blank) (blank) (blank) (blank) (blank)...................
View Replies!
View Related
|
TRACKER
