Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate. Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View Replies!
View Related
Sum The Total And Find The Average Cost
I need a formula that will scan column A (Code)total the like items (also) add column B (Qty) if there is a number greater than 1. Then add the price ($) together and divide by the sum of A&B. In other words find the average price for the total of each item.. A B C Code Qty $ PH06003000 1 1504.8 PH06003000 1 1582.24 PH06003000 1 1606 PH06003000 1 1504.8 PH06003000 2 3009.6 PH06003000 1 1504.8 PH06003000 1 1504.8 PH06003000 1 1504.8 PH06024000 1 2499.2 PH06024000 1 2499.2 PH06024000 1 1896.07 PH06024000 2 3909.66 PH06024000 1 2240.7 PH06024000 1 2259.4 PH06024000 15 30030 PH06024070 1 2039.4 PH06024070 1 1958.66 PH06025670 1 2521.2
View Replies!
View Related
Calculating Total From Number Of Minutes And Cost P/m
I need to calculate the total cost of outbound calls based on the total duration of outbound calls multiplied by cost per minute. For example, in a given month, the total duration of outbound calls is 261:16:34 being 216 hours, 16 minutes and 34 seconds. I have this figure in cell A1 with the format [h]:mm:ss. I then convert this to minutes in cell B1 by saying B1=A1, but having the format [m], which gives me 15676. In cell C1, I have the cost per minte value of £0.026. But when I apply the formula D1=B1*C1, I get £0.283, when 15676*£0.026 should in fact be £407.58.
View Replies!
View Related
Cost Based Mark Up
set up a cost based markup spreadsheet? I need to be able to mark up values from 0.01-9.99 by 20% of their value, and 10.00 and above by 10% of their value. I only know how to mark up values by a single percentage.
View Replies!
View Related
Calculating Cost Based On Several Factors
i. I currently have a spreadsheet which is used to forecast resource cost for a project. The forecasted cost is calculated on a few factors - rate, allocation, contract start and end date, and expected days worked per month. One of the mods actually helped me out with this a few weeks ago. I now have been told that there is a possibility that certain resource costs may change in the new year and that will need to be reflected in the sheet whilst keeping the historic information. For example - XXX has a rate of £200 p/d, allocation is 1, working 18.83 days p/m and is working from 01/01/09 to 01/06/09. The current formula will work out his cost per month until contract end. Now say his rate will be changed to £150 p/d from the 01/03 and all other info remains the same, I need the sheet to calculate his revised cost from 01/03 onwards and not change the calculation previous to that month. Now Ive actually managed to figure that part out myself by adding in two columns (over-ride rate and over-ride date) using a nested IF statement. The only problem is that if the new rate starts mid month then it will still calcuate the original amount for the full month and the revised amount from the next month. Edit - Also, could someone advise as to how do I remove my old attachments as I have almost used up my allocation.
View Replies!
View Related
Calculation A Total Amount
I am trying to write a calc for a total an amount based on a list choice Here is the Set up Fields A1 thru A255 are number 1 to 255 Fields B1 thru B255 are number 255 to 1 Field C1 is a List box set using A1 to A255 and its list Field D1 is set as the needed Calculation When you choose a number in C1 the D1 calculation should total the amount of Fields B* to B1, * being the corrisponding B field to the A field from the list Example C1=100 (Field A100) D1=Sum of Fields B100 to B1
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
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
Formula To Subtract Invoices From Total Amount Available
I have 2 tables, one with invoices, the other with purchase orders. I would like to have a "PO Amount Remaining" column on the invoice table that looks up the PO listed on the invoice with the PO $ amount on the PO table. Once it is matched, I would like to subtract the total invoices to give me the amount of the PO that is left. Basically I would like the "PO Amount Remaining" column right now to have $4,200 listed in each row for invoices 1 & 2.....
View Replies!
View Related
Sum By Name & Hours For Total Amount
I have pivot tables where I need to look through a series of names and sum the hours and dollars charged by that person. When I try to do a pivot I can get it to read column 1 Column 2 John Doe $4000 Mary Smith $ 500 But if I try to add the hour columns it breaks it out by the week the hours was charged column 1 Column 2 Column 3 John Doe $2000 4 2000 4 Mary Smith $ 250 5 250 5 I want it to just give me one dollar total and one hour total per employee.
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
Formula That Calculates The Total Rent Amount For Particular Dates
I need a formula that calculates the total rent amount for particular dates. Example would be a formula that includes a date starting at the first of the month (October 1, 2009 to September 30, 2010) and also would include a date starting at a date later than the first (October 3, 2009 to September 30, 2010). The figures needed would be the monthly rent (ex: $1,000) and a pro rated rent amout for a lease that starts after the first of the month. The pro rated amount would also have to include the years with a leap year. I have been working on this but I know there has to be an easier way.
View Replies!
View Related
Inserting A Total Amount Field In Each Empty Row
to run a macro that enters a new row after each set of currencies (which have been sorted). I now need to enter a total balance cell within this empty row which calculates the total amount for each currency. I need to do this using the SUMIF function below: SUMIF($J$3:$J$300,"L/C",$I$3:$I$300)-SUMIF($J$3:$J$300,"L/D",$I$3:$I$300) the above basically lets me calculate the total but as no - and + signs were input it does this by recognising the 'dr' and 'cr' in the column next to the amount.
View Replies!
View Related
Reference Cell & Add Amount If Positive & Subtract Amount If Negative
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do: I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
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
Prorate A Total Dollar Amount By A Selected Number Of Attendees
I need to prorate a total dollar amount by a selected number of attendees. How can I do that with code, I know the formula for excel, but how do I put that on my user form so it only does the math as a new entry is submitted? I do not want to have the formula on the spreadsheet which is my report unless there is an entry on the row, which will only be the case when a user completes the user form. I just do not want the div/# error on the sheet visible if at all possible.
View Replies!
View Related
Countif Formula: Added The Correct Amount In The Correct Cells
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View Replies!
View Related
Discount Based On Amount?
I need to create a price list a we give a discount based on the price of the product for example:.... All products over £100 have a 10% Discount, how do this and also at the sametime round the value up to 0.99p. ie Product ZXY123 is £123.99 10% off is £111.59 we need the calculated price to be £111.99.
View Replies!
View Related
FORMULA TO ADD AMOUNT BASED ON TWO Or MORE CONDITIONS
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View Replies!
View Related
Calculating Cost Per Second
I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network. Edit: Charges to network A is 1,79,- per minute after the first 20 minutes are spent. Charges to network B is 2,29,- per minute after the first 20 minutes are spent. To sum up: 1. The customer makes a call. 2. If there there are available free minutes, these should be spent first. 3. The customer is charged per minute, depending on network called.
View Replies!
View Related
Least Cost Routing
im working on a VoIP company, and having a hard time to figure out a result on a little time. the problem is finding a "least cost routing" wherein you will find the cheaper to higher price, and putting the results on a designated columns: I am including an image, sorry if the image seems to be heavy and large: as you can see: Column C contains the sale rate for the client, while Columns D to I, are the buy rates from our source carriers. While the columns K to P, are the columns where should one source carrier should be fitted base on the buy rates, that is best fitted to the sale rate. Red marks=are not suitable or non-profitable buy rate for the sale rate. Sorry for the bad english, but i hope you get what i mean. this is only a sample from our Internal data.
View Replies!
View Related
Calculating Cost
Problem - billing spreadsheet for prisoner fee. 1 - 8 hrs = $55 9 - 24 hrs = $55 + $65 or $120 Anything over 24 hrs - $65 for each additional (24 hrs) ($185) So if you were locked up for 6 hrs it is $55. If you were locked up for 18 hrs it is $120. If you were locked up for 28 hrs it is $185. And if you were locked up for 49 hrs it is $250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.
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
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Cost Vs Units Graph
I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company): Cost per unit for 0-10 units: $1 Cost per unit for 11-20 units: $3 Cost per unit for 21-30 units: $5 Cost per unit for 31-40 units: $8 Fixed cost: $100 (this is regardless of the number of units produced) Basically I'm looking to construct a cost vs units graph.
View Replies!
View Related
Transferring Cost From One Sheet To Another
I am trying to figure out how to update cost from a manufacturers price sheet in excel. I have one worksheet that has the manufacturer's part number and my current cost. A=item number B= current cost. on the second sheet is the same setup but my price list has roughly 3500 rows and the vendor price sheet is 23000 rows. Is there a way to automate the cost replacement by comparing item number and if they are the same replacing cost on sheet one with cost on sheet two?
View Replies!
View Related
Calculate X Percentage Of Cost
I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume $100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from $100 I get $90, however 10% of $90 is $9 which equals $99.
View Replies!
View Related
Cost Average Effect
Example number 1: You invested 10.000 Dollar in the year 1985 into a tempelton fund and waited 20 years. Result can be seen at many forums and stock plattforms Example number 2: In 1985 you had no 10.000 Dollar, but only 100 Dollar a month. You invested this 100 Dollar each month for 20 years. How big will the difference be between example 1 and 2 ? *** Background. If a "share" will cost 100 Dollar, you will get 1 share for 100 Dollar. Next month share is down to 50 Dollar, you will get 2 shares for your monthly 100 Dollar. Next month share is up at 200 Dollar, you will get only a half share for your 100 Dollar. Its called cost average effect. Tool should allow "play arround", to simulate various scenarios, talking into account growth rate, time period, monthly investment, fluctuation of a theoretical share. Idealy we would love to " import" e.g. famous fund history (eg. Tempelton, Pioneer) etc. to see how investment would have developed when a steady monthly payment would have been made.
View Replies!
View Related
Time And Cost Calculations ..
I am compiling a simple worksheet that will keep an ongoing track of labour costs in a production environment. The objective is to end up with a labor cost "per unit" for packing punnets of soft fruit. The source data I have is; 1 - Start time and end time of the job 2 - Any breaks taken during the job 3 - The number of staff it took to do it 4 - The status of the staff (Supervisor, temp, etc) and their hourly pay rates 5 - The number of punnets packed. With all of the above it should be a relatively simple exercise to calculate the cost per unit (and with a calculator is!). My problem appears to be that I am not formatting something correctly, because when I try to calculate the costs for the employees' my costs are obviously wrong. I have attached my early draft for reference, in the example shown I am showing that a supervisor earning £7.50 an hour worked for 1 hour 15 mins at a cost of £0.39
View Replies!
View Related
Cost Price Vs Quantity Discount
I'm trying to work out how to get a spreadsheet to calculate when it works out cheaper to buy more of something, due to quantity price breaks, than less... e.g. 1-10 are £12.50 each, but 10-20 are £10 each and 20-30 are £8.75 each.. so it's as cheap to buy 10 (10x10=100) as it is 8 (8x12.50=100) and cheaper to but 10 (100) than it is to buy 9 (112.50) than 10... but there are similar price breaks at 20, 50 +
View Replies!
View Related
Way To Check Whether Any Cost Centre Has Been Missed Out ...
I attached a workbook and it shows data in row 2 - 960. and in cells BA1574 -BM1604 are the summary by using 'sum if' function. As you can see, row 160 countercheck the results and it shows differences. So my questions are 1) any formula that i can use to ensure all cost centres are taken up in Col AZ1574 - AZ1604? I am sure that i have missed something out. 2) My goal is to summarise the total of each cost centre. Is there other options to reach this goal apart from using 'sum if' function? I would like to learn something new.
View Replies!
View Related
Manufacturing To Retail Cost Calculator
I am trying to make an EASY Manufacturing Cost to Retail pricing calculator. This calculator would have ability to include cost of goods, labor, markup etc of components manufactured and sold as retail products, example: small bookshelves versus large bookshelves or cabinets all have different materials (wood types, stain etc) in determining the final retail product costs that would reflect time of labor hours involved in producing to determine final retail costs. Should be simple in Excel 2000 (my version). I have the basic template created and have used Data Validation Drop Down Lists and utilized LOOKUP function. While my knowledge is limited in Excel, I am frustrated as how the LOOKUP function works, I can only get it to work where in the formula, the costs per unit are input manually, whereas I would prefer to have the data input automatically from columns of calculated wholesale cost plus markup per square foot data, ie: =lookup(A1, X1:X30, Y1:Y30) but get errors when doing this. Instead this works: =lookup(A1,{"pine", "oak", "birch"},{"3.99","7.87","5.15"}) and using this cell (A1) in my calculations for including the square foot costs in the final retail calculations. While this seems to work, it is not easily modifiable as costs change rapidly and would like to easily input the cost per square foot of the different woods in their own cells rather than in the formula calculation of the lookup. Hope that makes sense..... any suggestions? perhaps lookup is not the best function for what I am attempting? - sample file is attached, light green are notes of where my data is located.
View Replies!
View Related
ISDN Call Cost Formula
I have an Excel workbook with 2 worksheets, labelled as "codes" and "data" The "codes" worksheet contains three columns of data. Column A shows Country names Column B shows Country dialling codes Column C shows Cost per minute to the country ...
View Replies!
View Related
>>> Finding Lowest Cost/Vendor
I need to find the lowest price, mark it up and designate which vendor it’s from so the CSR knows which one to quote back to the customer. Besides going through it line by line, is there a way to do this? It seems like there could be a formula where it pulls the lowest of the 3 prices and vendor name into a new “price” and “vendor” columns and then I can go through and mark it up. Here’s a small sample of what the sheet looks like: Item #Desc V1 V1 CostV2V2 CostV3V3 Cost 123456brush Advance 2.56Weiler5.65Osborn3.25
View Replies!
View Related
Simple Job Cost Sheet
The job cost controller is not exactly what I need, so I have been trying to create my own. So far my progress has got me to be able to separate each laborer for each job. Here is my query: I have a drop down menu that lists ten different tasks(i.e. carpentry, painting, demo, etc.) and I want each of the ten tasks to separate themselves. Is this a vlookup function? How do I make excel listen?
View Replies!
View Related
|
TRACKER
