Model For Payment Schedule
Aug 20, 2013
I'm currently developing a model for a payment schedule. For example, lets say a customer purchases an item from a store, but the store receives that payment in the following month. I'm looking to develop a dynamic model so that if I change the assumption from 1 month to another term (2,3,4+ months etc.) the model will adjust accordingly.
I've started with a formula
=IF(MONTH(H3)-MONTH(G3)=B14,G11*B13)
But of course this will not work once the term is set to larger than 1 month. With B14 representing 1 month, and G11*B13 being payment information (price * quantity).
View 2 Replies
ADVERTISEMENT
Sep 30, 2013
I'm working on a report where i need to find the last date a payment was made by a certain client. The problem is that not all clients have the same amount of payments so I cant just choose a payment number in my formula.
Trishlast payment.xlsx
View 6 Replies
View Related
Jan 11, 2007
I have some Excel models out in the field with some old VBA code that is now causing problems. I regularly develop 'patch' models to make updates or repairs to user models, but need the VBA code syntax to get into a Worksheet module and change one line of VBA code.
The worksheet is named 'Forecast' and I need to change line 5 of my VBA code.
The vast majority of my users are on Excel 2000. I remember the security issues with newer Excel versions and can walk then through that, but I forgot the code syntax I need to get the job done.
View 9 Replies
View Related
Dec 2, 2008
This is a new payment status sheet that my company has put together to use with future clients. There are a few things that we would like fixed but can not figure out ourselves. I have attatched the document along with some notes as to what we can not get to work.
View 14 Replies
View Related
Apr 9, 2009
I would like to be able to choose a month (from a drop down menu), then choose who the payment will go to ie. creditor (from a drop down menu) ie. car, truck, cc payment, as a result of those selections, then enter the amount of the payment, and finally the ending balance will appear. I have the drop down menus in place and I have all the balance calculations in place. I could use the spreadsheet that I have but I only want to see the below info. Plus I don't know how to link or lookup this data to the stuff I've already created.
Select Month:
Select Creditor:
Enter Payment:
New Balance:
I have already set the sheet up calculating the balances after a payment has been made. I know how to hide all the formulas, but I don't know how to get it to so that you choose a month, then creditor type (from drop down menus), and payment so that you see a new balance. I only want to be able to link or lookup the month, creditor together so that a payment can be made to that cell that I've already established. As a result, then that cell can be shown in the new balance.
View 14 Replies
View Related
Feb 8, 2010
Not sure if this is the correct section for this kind of query but I'd like some assistance with a calculation that I can't seem to figure out.
Essentially it's for calculating night payments for our employee time-sheets. Our staff have very sporadic shifts and are paid extra for working between the hours of 00:00 and 06:00, basically when employee's enter their start and end times I'd like the spreadsheet to automatically calculate how many hours they have worked between those hours, I imagine it's very simple but I cannot figure out which function to use.
To complicate matters, because staff can work shifts which start on one day and finish the next we work on a 48:00 clock basis so its' not only between the hours of 00:00 - 06:00 where they qualify for night payments but also from 24:00 - 30:00 if that makes sense?
View 9 Replies
View Related
Aug 12, 2013
I am trying to set up payment record sheet as follows
First Tab: This is the total value of each item, with the payment date in cell C1
Second Tab: Is the payment date of the item
Third Tab: Is the payment due tab
So what I want to do is enter the payment in C1 on the first tab. Then on the second tab I want to enter all the items that want paying on that day, but this will also have previous payment dates shown as a sort of record of what was paid on what day.
On the third tab I want it to show all the payments that correspond to the same date as C1 DATEVALUE
And on the gross tab I want to show all payments to date including the current date.
I have got the payment date to work but I cant get it to show all the previous values.
View 1 Replies
View Related
May 2, 2014
I have a list of several hundred loan recipients who are all starting to pay their loans back on different dates. I am able to get the calculation to work so that I just need to put in the start date and excel works out the other 5 or 6 payment dates.The problem is, some people pay back on the 29th, 30th of the month which is giving me a headache when they are supposed to pay in February. The formula I am using calculates the next date of payment to be on the 3rd March (skipping the February payment all together).
The formula I am using is : =DATE(YEAR(A10),MONTH(A10)+1,DAY(A10))
View 3 Replies
View Related
Aug 21, 2008
I have a situation where I need to highlight different customers based on what there terms of payment are. Conditions 1 and 3 seem to be working fine but for some reason condition 2 is not working at all. I made sure all names are exactly as listed in the A2 cell....
View 9 Replies
View Related
Aug 24, 2006
I have been raken by brain for a simple formula to calculate an Interest Only Payment on a mortage note, example,
sales price = $162000
term = 360
interest = 8.5
I was able to calculate the payment of principle and interest, but I need to validate what would be an Interest Only payment on this?
View 5 Replies
View Related
Nov 3, 2006
I used erecord the other day to do my activity statement for the first time (I have just started a home based business which has not yet started trading but I had to send the BAS for the purchases that I had made for the business) and it was quite easy to use and you can send it electronically to the ATO which saves a lot of hassle particularly as I am very not accountancy litterate. However I am trying to develop an expenses/payment spreadsheet similar in function to erecord but that allows me to categorise the inputs.
My headers are:
Date
Cbookref = (drop down validation box) similar to a chart of account #
Category = ie - advertising accounting fees etc.. uses a look up function with cbook ref to populate field
Description .........................
View 5 Replies
View Related
Mar 12, 2007
i have myself a table on the left is all the members names and along the top are 12 dates (1/1/07, 1/2/07 etc).
In another sheet i have a drop down box for the name of the person and a dropdown box to select a date and then a space to enter the amount paid so i need a macro that will find the name and date i selected from one sheet and take the amount paid into the other sheet and insert the amount in the correct place.
If it is unclear what i mean i will post screenshots tomorrow.
View 12 Replies
View Related
Jul 7, 2013
How can i get the actual Payment due date from no. of days using macro. i. e.
Posting Date Dues Payment Date
------------ ------ ---------------
22.06.2013 21 days ???
View 9 Replies
View Related
Nov 29, 2013
I have a file which student Monthly Loan Payment schedule.
I want to make a list which shows all payment period by installments and payment amount , dates by Student names.
Attached file, you can see all detail and wanted LIST.
View 5 Replies
View Related
Mar 14, 2009
How to calculate a One time Payment to maintain the Tenure In Amortization?
Ex:-
I havea loan of Rs 1,00,000/- to be paid @ 8%/12 for the first year and then from 13th month , it will be Floating Rate of Interest which would be mostly higher than 8%/12...
I will be paying this loan off in 5 years ( i.e. 60 months)
and the EMI = Rs 2028/- per month..
Lets say, if the Interest goes up to 12%/12 after the 12th EMI then the tenure increases by 5 months from 60 months to 65 months thus adding to the cost of Rs 2028*5 = Rs 10140 more..
Now What amount do I need to pay alongwith the regular EMI's to maintain the tenure of months?
The amount Im referring to is a One-Time Payment added with the EMI for the 13 the mOnth to reduce the Principal and accordingly the tenure?
View 14 Replies
View Related
Mar 4, 2014
I have a capital lease amortization schedule with annual increases to monthly rent that I am trying to solve for an interest rate such that the balance nets to zero at the end of the term. I am calculating on a monthly basis; in other words, principal minus monthly payment plus monthly interest expense equals ending monthly balance is calculated in each row each month. The present value is known, payment term is known, future value is zero, and the payment amounts increase annually. These assumptions may change for new leases so ideally the solution would be dynamic, adjusting for shorter/longer terms, etc. Here is an example of my assumptions:
Payment start date: 8/1/13
Term end date: 10/31/2025
Rent length in months: 144
1st months rent: $500,000 - payments are due at the beginning of the month and are paid monthly
Annual rent escalation: 3% - i.e. 1st 12 months is $500k/month, 2nd 12 months at $515K, etc.
Beginning NPV: $75M
Ending value: $0
Imputed annual interest rate: UNKNOWN
I'm not sure if this is relevant, but the monthly payment is allocated between principal and interest. Monthly interest expense is calculated as the current balance * (imputed interest rate / 12).
Currently, I've plugged the interest rate such that my ending balance is 0, however I was hoping to calculate it on the fly as opposed to manually plugging it.
how to calculate annual interest rate with these inputs? Is there a way to make the rate function work with payment increases?
View 7 Replies
View Related
May 24, 2006
I'm building a real estate payment calculator. I'm using the Payment (=PMT) function in Excel 2003. When I add the information that it asks for in its wizard, I receive the correct numerical figures but with a minus sign in front of it making the result negative, i.e. "-$630.13" instead of "$630.13."
Is this file corrupted or have I put something in incorrectly?
View 3 Replies
View Related
Aug 11, 2013
i am trying to get the number of days from last time ticket paid till todate, considering the day & month of the hiring date but the year of the last time paid. say AAA hired on 15-Nov-2001 receives a ticket every 24 months; received last ticket in Nov 2011.
i want to count the number of days between 15-Nov 2011 until 31-Jul-2013 (dd & mm are from joining date yyyy from last time paid) i have a list of 1200 names with different dates of joining and different dates of payments.
View 6 Replies
View Related
Nov 18, 2009
I have 2 production unit : A; B
They make payments to suppliers on certain periods : p1, p2..
The payments are on variable and different currencies : EUR, SEK, USD, CHF,AUD..
I explained the sample case table in attached excel sheet. My question is "how can i create the summary table easily and quickly by excel?"
currently I am calculating it by a simple excel formula (vlookup) that I need to change the range for each time. It s very time consuming and easy to make mistakes..
View 3 Replies
View Related
Apr 13, 2014
1. In column A, I have models, in column B, I want to place either a "printer" if the model begins with Phaser or Laser, and if it begins with WorkCentre make it MFD. Also, if the Phaser has MFP on the end, make this an "MFD" and If Color 560 or Docucolor, make this "Out of Scope". How do I read the first part of the string in column A to do this, or do I need to make a second column with this Phaser and WorkCentre broken out?
I have this code attached, but only works if I have the word Phaser or WorkCentre.
View 8 Replies
View Related
Dec 20, 2006
I want to create a button on this spreadsheet - the button will be called z4 in the "Yr 9 ICT" sheet
it will include the following
rows Jane Bloggs 2
rows Jane Bloggs 12
rows Jane Bloggs 22
rows Jane Bloggs 32
rows Jane Bloggs 33
rows Jane Bloggs 42
rows Jane Bloggs 44
rows Jane Bloggs 66
What do I have to do to create this.
View 10 Replies
View Related
Jul 1, 2007
I have a sheet with a table of peoples hours they are doing during the week each day. I want the hours to be put into a model shading the appropriate times to show when people will be in. I have attached a sample sheet of what i want it to do which i have had to do my self. Sheet 1 has table in and sheet 2 has the model i want to be automatically filled in once ihave entered all my data in the original table.
View 11 Replies
View Related
Nov 15, 2006
I am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).
ComponentMTBF(months)
115
224
Below are Company B's sales records for the past 36 months.
MonthSalesMonthSalesMonthSales...............................
View 5 Replies
View Related
Jul 1, 2009
I have a list of employees who's job status may change at the end of a given month. If the status changes, they have a supplemental payment that is tied to their job status and may change as well. But that depends upon certain conditions like their years of service and their union.
I'm trying to write a formula that will update that supplemental payment based upon certain conditions, but I can't quite seem to get it right. A challenge is the table I'm looking values up in has 8 columns and the value could be in any of the columns depending on years of service.
These are the conditions for the lookup:
1. If their union local is the same, they would keep the supplemental payment so long as it is not more than allowed for the title. If it is more, they get the supplemental that corresponds to the new title and the years of service.
2. If the new title is PAA and they have 10 or more years of service, the new supplmental is $600.00
3. If the new title is Clerical Associate II and they have a 6 or more years of service, they get a supplemental that corresponds to their years of service.
View 4 Replies
View Related
Mar 21, 2014
Salesman
Sales Amount
Introduced by
sales
introduced
sales
indirectly introduced
sales
Tom
300
none
Tom
300
[Code] ........
I have salespeople who I want to pay a bonus to but I've also offered them a bonus on how much the salesmen they referred to the company sell and this goes like a pyramid down 3 levels.
so I have a list of salesmen next to there name is how much they have sold then who introduced them. from this I need to make a list of each salesman and below all the people he introduced and the people they introduced with the sales in the cell next to them.
To show how it could look I've done an example above of the information I need for tom, as you can see all the data is in the first 3 columns and tom needs to show that he introduced bob & Sue, but because Bob & Sue also introduced someone, tom needs to show he was involved in this as well and jo, terry, mark need to show, preferably in a different column as the bonus for them is lower.
View 5 Replies
View Related
Jan 27, 2007
When a date is entered e.g 1/27/2007 The bill payment dates should all be automatically calculated under the “Date” column. Rules for the date
Rule1. e.g. 1 Month—the date should be 1 month after the date entered (as shown by the simple example 1/27/2007 one month is 2/27/2007 in the sheet)
Rule2 if the date entered is the last day of the month, the calculated date should also be the last day of the month provided it is a working day.
E.g 1/31/2007 one month is 2/28/2007. (provided 2/28 is working day)
2/28/2007 one month is 4/30/2007 (provided 4/30 is working day)
Rule3 If the calculated date based on rule1 and rule2 is a non-working day, the date is pushed back by 1 till it becomes a working day. e.g 1/27/2007 two month is 3/27/2007 if 3/27/2007 is Saturday it becomes 3/28/2007 which is a Sunday, another non-working day. So the output should be 3/29/2007
Rule4, under rule3, the date cannot be pushed to the next month. Instead, we push the date forward. In the previous example assume 3/29, 3/30 and 3/31 are all public holidays, by rule3, it will be finally pushed to 4/1. But this becomes another month. So we push forward instead to 3/26 which is Friday. The output is 3/26/2007
Definition of working dates (Monday to Friday excluding the holidays listed in the sheet).........
View 3 Replies
View Related
Dec 27, 2013
I have a deferred revenue model. Revenue from each month earns out at an equal rate over twelve months. Sales for each month are in column B (B21) and C. I created formulas for the first year but when I get to the second year, I'm not sure what to do, because the earned premium will include values from previous year sales and current year sales.
View 4 Replies
View Related
Dec 14, 2009
Suppose that you have some data points and you want to fit a straight line to them. You write the line's equation in the cells ajacent to your data points, make the square of each difference, sum up all the squares and you have your objective function. Of course your line equation depends on two parameters, that you enter in two cells. Then you run the Excel Solver, enter your objective function as target cell to minimize, enter the range containing your parameters, set your constraints and off you go.
So far so good.
And now suppose that in order to generate your model data it is not enough to write a straight line equation in some cells, but rather to run a complete reactor model (contained in a macro). How do I couple that model-generating macro with the Excel Solver? Now, I actually found a solution. I wrote a Worksheet_change macro in the sheet containing the model parameters, which calls the model-generating code each time the parameters are chenged by the Excel Solver. This has worked fine for a simple example as the straight line one.
Now one of the two:.....
View 4 Replies
View Related
Nov 15, 2009
What would be the best way to put together a cancel curve on an annual membership model?
View 5 Replies
View Related
Feb 12, 2013
I have a question around making a summary table from a model. I will try to explain this as plainly as possible.
I have a model where you can toggle 3 different inputs across two variables. The final output number of the model will vary accordingly.
I would like to create a 3X3 table with the final output for each combination of variables. Is there a way of doing this using formulas or would I need a macro?
View 2 Replies
View Related
