Cumulative Interest Formula
I have a person who was paid £1000 pension pa for ten years. I've found out that the pension should have been split 50/50 with half increasing by 5% pa. Thus in year 1 total pension would be £1000. In year 2 the total pension would be £1025 ((£500 x 1.05) plus £500)
I know that in year 10 the total pension should be £500 (non increasing) plus £814 (£500 x 1.05 to the power of ten). My problem is how do I work out a formula which calculates the total arrears due in year ten? I'm thinking the arrears due after the ten years is £314 but something is telling me it's a lot more.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Cumulative Total Formula
D17 is a cumulative dollar total (yeartodate) of the monthly changing dollar amount in D5. Each month I manually add the D5 figure to D17. Is there a formula that will automatically update the D17 cumulative total with the everchanging D5 monthly figure?
View Replies!
View Related
Compounding Interest Formula
I am trying to set up a budget for my self. Of course I am trying to make it as complicated as possible. I have done searches online and that is how I found this forum. I am finding amortization formulas, but not what I want Here is my question. I have two fields, Mortgage and auto loan I want to have a field that does a calculation for me. I know following fields as an example making numbers up Interest rate is fixed at 5.5% Amount left on loan is 150,000 Loan is fixed at 30 years with 28 years left I want to run a formula so when I make a monthly payment of 1000 dollars how much of that goes to interest, how much to principle (not to make this complicated even more, but let say that I have 200 escrowed which I am not sure if that has interest calculated on(I don’t think so as it is property tax?)) Same example for car loan. 5 year loan, 4.5% interest, payment 438 a month.
View Replies!
View Related
Interest Only Payment Formula
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 Replies!
View Related
Cumulative MAX Formula UDF Help
Is it possible to arrive at the formula result in cell (C9) using only the cells in column A and bypassing the need to have a column B? I originally tried =MIN(A1:A9)MAX(A1:A9) as a way to bypass column B and have a single formula but it did not give me the same result. I was thinkging I might need to have a (UDF) but not sure. Thanks Again. Column A Column BColumn C A1=2.53 B1=A1Max(A$1:A1) A2=3.52 B2=A2Max(A$1:A2) A3=5.47 B3=A3Max(A$1:A3) A4=6.87 B4=A4Max(A$1:A4) A5=7.89 B5=A5Max(A$1:A5) A6=4.14 B6=A6Max(A$1:A6) A7=3.23 B7=A7Max(A$1:A7) A8=2.10 B8=A8Max(A$1:A8) A9=12.21 B9=A9Max(A$1:A9) C9=MIN(B1:B9)
View Replies!
View Related
Quarterly Interest Calculating Formula
I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly. But I want to make it compact , as d one I created is long enough. A3 = Principal Amount B3 = Date of Investment C3 = Interest as on Date D3 = Number of Days , amount Invested {comes out of formula set} E3 = Rate of Interest Now in F3 I want the Interest amount , compunded quarterly. Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?
View Replies!
View Related
Compound Interest Rate Formula
I am trying to calculate the effective annual interest rate earned on an investment and find the results are close but not really accurate. I suspect because I have not included the frequency of interest in my existing formula r = n * nt root (A/P1) where; r = the effective interest rate n = the number of times interest is added per year t = the total number of years A = the current value P = the original value The 2 problems I face are; 1. Confirming this formula would provide the correct answer (need maths expert here) & 2. How would "nt root" (as in sqr root, but using the product of the years and frequency) be used in Excel
View Replies!
View Related
Formula For How Much Interest Would Be Paid In A Given Timeframe
I am currently trying to create an excel spreadsheet where I would know how much interest I will pay in the next six months, since the balance changes every month I am not sure how to formulate the total interest paid with in a specified time period. I currently can calculate the interest and fill in the below rows with each month but I would like to simplify the report so that I can add the Balance, Rate, Payment(2% of balance), and Time period and in another cell it will tell me how much interest I will pay in that time period. Bascially I would like to keep all the calculations in one row. For example Balance  Rate  Payment  Time Period  Interest Paid $1,000  10.00%$20.00  6  $48.56
View Replies!
View Related
Cell Lock, Interest Calculator Formula
1. Can I lock particular cell If Yes,How? I want to lock cells which contains formulas. 2. If cell K7 shows 11 I want to see G11 in H9. Same if Cell K7 shows 201 I want to see G201 in H9. Its a interest calculation sheet where K7 is number of days amount invested and COLUMN G shows interest accured.
View Replies!
View Related
Updating Data Formula For Cumulative Log Plot Graph
I have attached 2 files. One of them is called Macca.xls. This is the file that was created for me by shg on an excelforum. The other isOreDep_updated.xls  this file is the updated list that I want to convert into the Macca file or make it look like the Macca file. I awas trying to make a couple of plots, but they were not llooking like some examples I have. In the OreDepupdated file attachment is my data I needed to use. I needed to make 2 plots: Plot 1: Resource Grade (log scale) against the proportion of deposits (linear scale) Plot 2:Tonnage (log scale) against proportion of deposits (linear scale) This was done with my old data that is shown in the Macca.xls attachment. They are meant to cumulative proportion plots. As a line or scattergram. An example of what they are meant to look like is at: http://www.nbmg.unr.edu/dox/ofr962/c11.pdf on Page 4 and 5. shg managed to make them look like I wanted with the log normal smooth line too. My problem now is that I want to change the data under Name, Resource Tonnes and Resource Grade (basically replace the old data in Macca.xls with the new OreDep_update.xls, to come up with same type of plots). But whenever I try to copy and paste the data into the Macca file it messes up everything. I looked at the formula that was created and I really havent a clue how to change it, without affecting everything else like the plots. I also need to add 2 new columns for date and for reference, and it wont let me do that.
View Replies!
View Related
Create A Formula To Add Values Assigned Cumulative Dates In A Month
I have a variable list of items in date order, there could be one, more than one, or, no items for any one date Each item has it's own line. I need a formula in a separate column (column F) to total each day's items. Column A includes the Date. Column F is a cumulative total (as in Column C) for items on the same date. Column C=D+E. I am looking for a formula which will add up items in column C if they have the same date in column A, and put the answer in the last cell in column F for that date. Example........................
View Replies!
View Related
Cumulative Value
I would like to work out the cumalative value ffor each row on a sheet I have. Basically it just needs to start from column 4 each time and then go across and take first value and then add that to the second one, take that answer and add to third one and so on. Basiaclly I need to get an end result on clolumn 25 each time. The code has to be able to count down the rows too.
View Replies!
View Related
Cumulative Sum
I need a cell that holds a cumulative sum , but i get stuck into cell self reference. Let me explain . Imagine i have cell A1 which holds a formula : =B1 + C1 If i keep changing the values of B1 and C1 the value in A1 will be changing as well . I need to the sum up of A1 values at any given moment. I practically want A1 := A1 + B1 + C1
View Replies!
View Related
Cumulative Totals
This problem has come at the end of a big exercise whereby I've managed to construct a spreadsheet that automates three payment processes based on various VLookups to another file. However, I've come to a stage where I need to preempt a payment allocation based on cumulative totals. I'll try and explain clearly below but I've also attached a spreadsheet showing the intended result (along with a copy without the output so that someone can add in the formula  again, if it exists. So, this all centres around a code allocated to our clients and a declaration that they complete. Say client code "Apple1" (col E) sends in instructions to pay on a position of "650,000" (col F). Within our existing spreadsheet we have an available position of "800,000" which can be seen by totalling column G for client code "Apple 1". So that's the first requirement  for all rows on my existing spreadsheet for client code "Apple1", I want to add the cumulative total of column G ("800,000") I'd like this captured in column H, as per my example. We can then make payment based on their declaration, up to their total available amount. However, we need to pay them in stages according to our existing allocations (col G). Therefore, taking the first example, their declaration show's an available position of 650,000, their total position is 800,000 and the available nominal amount for that row (col G) is 200,000  so they can be paid on that full amount therefore, 200,000 should be shown in cell I3. So now, they've got 450,000 left to be paid and cell G4 again shows a position of 200,000, so once again, cell I4 should show 200,000. The client "Apple1" now has a payment amount remaining of 250,000 remaining but in this instance, cell G5 is for 300,000  so because the remainder of their available payment is less than the amount in cell G5, we should post the available payment amount in that field  250000. Therefore, if you add up all of column I for client "Apple1" it comes to 650,000  the amount we have on our declaration  even though the total of their available position is 800,000. I've included another client in the mix "Sauce2" who should show 200,000 and 0 respectively in cells I6 and I7.
View Replies!
View Related
Cumulative Subtotalling
Need to insert two rows, then sum column C and E based on cell value change on Column A. ...then also would like to get the summed value of each total eg,. (starting total should be where last cell should always equal the sum of first calculated cell. D6 = C6, then =C10+D6, and C16+D10, etc...) to be summed with next total down, and so forth to end (See attached)
View Replies!
View Related
Interest Functions
The file I am working with is available here (1) I need to work out the value of 2 years worth of interest on a loan  details would be Loan = 30,180.00 interest = 14.90% pa (calculated daily) term of loan would be 60 months I am currently using an ammortisation speadsheet which calculates the interest per month etc ... at this time I simply highlight and calculate the interest which would be two years worth ... the problem is this calculation varies from the bank interest calculations (although the repayments are the same and the total interest payable amounts are the same) The banks calculations are said to be done on the basis that the minimum monthly repayment is met and there are no additional fees thrown in ... so I cant understand the difference ... (2) In addition to the above question, but working on the same loan, traditionally amortisation tables calculate the amount of the repayments, the interest component and the principal component ... some include a column where you can factor in additional repayments on a row by row basis (like the one I am referring to) the table assumes that the payment is made on the due date ... what I would like to be able to do is (a) insert the actual payment dates (b) if no payment is recorded for that period, automatically insert an overdue fee
View Replies!
View Related
Interest Calculation
I have a macro that formats a spreadsheet to show outstanding invoices, grouped and subtotalled by month. To add to this I need VBA code that will use the subtotals to calculate interest on overdue accounts. Interest becomes due a calender month after the month in which the invoice is dated. So for example a January invoice would start to accrue interest on 1st March. Below is the subtotals code (sadly the totals don't adjust if data is added or removed but perhaps that is another question for another day.) Dim LastRow As Long Dim NextMonth As String Dim R As Long Dim Rng As Range Dim SubAmount As Currency Dim ThisMonth As String Dim TotalAmount As Currency Dim Wks As Worksheet Set Wks = Worksheets("Reconciliation") LastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Wks.Range(Cells(2, "A"), Cells(LastRow, "D"))
View Replies!
View Related
Compounding Interest
attached the spreadsheet that I need help with in that same thread. Please check out the spreadsheet via: [url] Here's my issue: I'm trying to be debt free roughly around 1011 years years based on my current plan. SO, what I am trying to do is figure out what I can do with my savings at 1, 2, and a 3 % rate of return in a savings account that is compounded monthly after that debt free point. My time frame for results are 51015and 20 years.
View Replies!
View Related
Calculate Cumulative Percentage
Microsoft Excel  Book1___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB5= BCDEFGH5JanFebMarAprMayJunTotal63%2%5%8%5%5%???Sheet1 I have this example above which i want to know what is the total % change from Jan to Jun. In the total column, the result i got is 19% using formula =1*(1+B6)*(1+C6)*(1+D6)*(1+E6)*(1+F6)*(1+G6)1. Is there any other more simplified or better formula that can be used actually?
View Replies!
View Related
NonCumulative Running Total
After about 3 hours of online research, and maybe an hour or so of experimenting with the IF command, my mind is blown and I, essentially, have given up! I have a spreasheet with each column representing a month, then four more colums to represent quarterly values, and one other for a summary of the entire year. I don't want a cumulative running total. For example, if I enter a value for January, that value should appear in the 1st quarter column, and yearly summary colum. Now, if I enter in a February value, the 1st quarter column should display the February value. I need the 1st quarter column to "update" for each of the three months within the quarter. Likewise for the yearly column except, obviously, I need it to update for each of the 12 months. I started toying with the IF function, with limited success, but there MUST be an easier way?
View Replies!
View Related
Cumulative Total By Month
I have a list of daily sales which I would like to have a cumulative total by month. Once the next month is encountered, the cumulative total will reset for that month again. E.g. (please see attached file) Column C is my cumulative total. The cumulative sum will reset when the month changes from Jan to Feb. I can achieve the results using VBA but I need to distribute my report to parties whose VBA environment is disabled. So, I need to work around this with a formula.
View Replies!
View Related
Percentage Of A Cumulative Target
Cells D 5 to D 36 are representing day 1 to day 31 of the month. clocked working hours of the employees will be entered to these cells. they are supposed to meet the target of 8 hours clocked at the end of each working day. that means on day 1 the target is 8 hours, day 2 it is 16 hours, day 3, it is 24 hours. i want to see the cumulative progress in ( percentage) on any give day. That means on each day the clocked hours should be compared with the actual cumulative target until that day.
View Replies!
View Related
Cumulative Value From Quantity Lookup
I'm guessing it will be some sort of nested loop but I'm just going round in circles trying to solve it. I need to calculate a cumulative value based on the following criteria: 1. Pre Qty  can be any integer value 2. Qty  can also be any integer value Accumulate values by looking up the Pre Qty + 1 then add all quantities * values based on those provided in a table giving different to  from ranges. Attached is a spreadsheet
View Replies!
View Related
Calculating Cumulative Total?
in my worksheet i have different kind of items with its cost. in my case which is not in order, that is, the order of items can be AABAACCBA. I want to calculate Cumulated Total on each row. but i am not sure how to achieve this by conditional formula? the values in my sheet looks like the following, Date ITEM TYPE AMOUNT Cumulated Total 10Jan07 BookA1010 value(Book) 11Jan07PenA515 value(Book+Pen) 12Jan07TableB1515 value(Table) 13Jan07PencilA2035 value(Book+Pen+Pencil) 14Jan07ChairB2540 value(Table+Chair) 15Jan07SofaB3575 : 16Jan07RoseC2020 : 17Jan07Calc...A3065 : 18Jan07JasminC1030 value(Rose+Jasmin) find the attachment for reference. How to achieve this using conditional statement or lookups or someother? and i try to avoid macro.
View Replies!
View Related
Calculating Compound Interest
I have a column of years and a column of numbers representing annual amounts placed in a savings account for the year. I would like to calculate the balance of interest earned added to the balance of the account and then calculate the interest earned on the accumulating amounts each year. The results would be the account balance displayed in an adjoining column. So far, I have not found a worksheet function for that. Could someone point me in the right direction? Perhaps there should be several columns of data?
View Replies!
View Related
Calculate Interest Per Month
Sample data: 020609 to 180609 earned $2500 190609 to 230809 earned $3500 240809 to 311209 earned $4500 I am thinking if there's a formula that can calculate the interest earned for each month from Jun09 to Dec09.
View Replies!
View Related
Display Rows Of Interest Only
I have this sheet at work which contains a bunch of courses my employer runs, alongwith the dates and other details (which spans columns A through O) Courserows which require action/followup are highlighted with a certain color. The "completed" courserows have no fill. Note: the courses requiring action may have a past date, the only distinguishing feature is the highlight color. Is there any way by which i could generate a popup box of some sort which would display the "highlighted" rows (maybe from A through I or something). I could add 'followup' flags on another column if the highlight color won't do.
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
Show Only Records Of Interest
Following is the excel database. B11 is the dropdown menu. When I select the team from drop down menu. it shud give me sorted Name list from B12. I tried the following formula: =IF(INDEX($C3:$E$11,MATCH($C$12,$D3:$D$11,0),2)=$C$12,INDEX($C3:$E$11,MATCH($C$12,$D3:$D$11,0),1)," ") But it is repeating the name or leaves the cell blank ( which i don't want). When I select Team1 it shud give me Names : a,d,e (without blank cell), Even i tried Array and some vba programmimg but it ........... ABC 1SrNameTeam 21aTeam 1 32bTeam 2 43cTeam 3 54dTeam 1 65eTeam 1 76fTeam 3 87gTeam 3 98hTeam 4 10 11Team 2 12b 13b 14#N/A 15#N/A 16#N/A
View Replies!
View Related
Count Of Cumulative Percentage Of Total
I have a table representing the usage of several thousand product in a market. Each row represents a product and one column indicates the frequency of use. I'd like to find out how many products represent the Nth percentage of the whole, when ranked from most used to least. Shorter, if I want to know the Top 25%, how many products equate to the top 25% of all product frequency? ....
View Replies!
View Related
Cumulative Returns For Dynamic Range
I would like to be able to calculate cumulative returns by selecting a starting and ending quarter from a validation list and finding the product of all the quarters between the two selected. I imagine the equation would look something like this: ...
View Replies!
View Related
Cumulative Sum In Loop Using Variables
I'm having difficulty with concatenation and the use of a string variable. On my attached spreadsheet I have a row called Must Fund Tasks. The binary variable in the cell is entered by a selection on a user form. If the value of the cell is "1" then that particular task must be funded. The code I developed forces solver to keep the binary value of 1 in that particular cell when looking for the optimal solution. This same row of binary variables is the row of values that I have solver change to find my optimal solution. My problem is that I have another set of contraints based on mutually exclusive tasks. If there is a binary value of "1" in at least two of those cells then only one of the tasks may be funded and they are said to be mutually exclusive. In other words, if the user form indicated that tasks 1 and 5 were mutually exclusive then the binary values are Task1=1 and Task2=1 and the constraint would be Task1+Task2<=1. The code I've written looks for a value of 1 in the Mutually Exclusive row and if it finds one it adds the name of the corresponding "Must Fund" cell 4 rows above to a set called ExclusiveSet. ExclusiveSet is defined as a string and I'm having trouble with the concatenation, I keep geting a "+" out in front because of the intial loop when ExclusiveSet="" ( it returns "+$F$29+$H$29"). I'm also not sure if Range("CalcsStartcell").Offset(I + 15, 3).Formula = "=ExclusiveSet" is the appropriate way for me to return the value of the ExclusiveSet in the desired cell. Sub constraint() Dim p As Integer, CalcsStartcell As String, I As Integer, T As Integer T = 7 I = 5 CalcsStartcell = "C16" 'setting Mutually Exclusive constraints With Worksheets("Sheet1").Range(CalcsStartcell) For p = 0 To T  1 If .Offset(I + 12, p + 1).Formula = "1" Then..................
View Replies!
View Related
Interest Template: How To Sum Up According To Date
I have created a template that I use to determine interest calculations (it is based on simple daily interest, but monthly compounding and adjusts for any variations in interest rates during the month), and it seems to work fine so far. However, I am trying to make it better and would like to eliminate as much manual input as possible. I have attached an example of the template to help explain what I would like to achieve The Running Balance is dependant on column "J"  Interest for the Month. At the moment, I am manually adding up interest accumulated for the month, however if I add one cell too high or miss a cell when adding, it can lead to compounded problems further down. So in short I am looking for a method that would automatically calculate "interest for the month" by adding all the amounts in column "I" (Interest Daily) at month end for that month only (dates are entered in Column B) In the attached template, grey areas indicate capital movements and/or interest rate changes that may occur during a month (ie movement in the month in question that affects the daily interest calculated) I hope I've covered everything! Any help in this regard comes most appreciated. If anything is unclear, I will do my best to explain what I am trying to achieve in greater detail.
View Replies!
View Related
Grid To Determine Interest Rates
I have a grid to determine interest rates. The are add ons to the rate that are dependent on two factors  the credit score and the Loan to Value percent or LTV, both of which are shown in terms of ranges (720739, etc). See the screen shot below. I have already written formulas for converting the score and LTV to the ranges as they appear on the sheet (ie, if you type in a score of 722, the formula converts it to the range of 720739. What I need to do is this  when the score and LTV are input and determine which set of add ons come into play, I need the sheet to take those addons, identified by an x next to it at the top of the page, and add them to the base interest rate, giving me the final rate. Again, see below to make this more clear. I've thought of using VLookup, but I don' think that would be applicable here. What's the best method, and can you give me an example of what a formula might look like? Loan Level Pricing Adjustments Base Rate 6.25 6.25 Credit Score 741 >740 LTV 60 740 Cash Out 0 0 0 0.25 0.375 0.375 n/a >740 Investment 1.5 1.5 1.5 2 2.5 2.5 n/a >740 2unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 >740 34 unit 1 1 1 1 n/a n/a n/a 720739 all 0.25 0 0 0 0 0 0 720739 Cash Out 0 0.125 0.125 0.375 0.5 0.5 n/a 720739 Investment 1.5 1.5 1.5 2 2.25 2.5 n/a 720739 2unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 720739 34 unit 1 1 1 1 n/a n/a n/a
View Replies!
View Related
Calculate Daily Interest Rate
Some years ago I came across a formulae to calculate Daily Interest on a Building Society Savings account in the UK. I have used this since but find my calculations never work out the same as my BS, although to my advantage! It is =B3*B4/360*DAYS360(B5,B6,TRUE) Where: B3=Capital B4=Interest Rate B5=Starting Date B6=Finishing Date For some reason the formulae uses 360/year and not 365/year. Using both still gives wrong answer.
View Replies!
View Related
Cumulative Total With Single Data Entry
I would like to be able to enter a number in a single column, and then Excel adds this number to a total column each time it is updated. This is for entering via a PDA, so I need a simple entry method, ie to enter a figure, and then keep a running total every time a new figure is entered. This would only use the one column and overwrite the number each time, rather than adding up individual columns. The attachment shows the items listed in column A, column C3  C10 would be where I would like the number of items entered, and as I go round, I would just update the last figure. I would like the figures from C3  C10 to be added to the Total column D3  D10 for each item.
View Replies!
View Related
Cumulative Sum (convert Into Terms Is SIGMA)
I have, for my formula, 2 constant values a and x, I then also have a variable, n. The formula I'm trying to convert into excel terms is SIGMA (with respect from 1 upto n) ((1x)^(1n) * x)*a. Unfortunatly thats not drastically inteligable so i've attached a picture file that should explain it in more understandable terms. Now the problem im having is, sigma is a cummulative funtion, which in this example is relative to the variable n, firstly I don't know what formulas I would have to use in order to get a sigma/summination function, and secondly I am uncertain of how to link it to a variable range.
View Replies!
View Related
Conditional And Cumulative Subtraction That Starts Over Again When The Value In A Column Changes
I am attempting to perform a cumulative subtraction function that starts over again when the value in a column changes. I have a list that is sorted by a column for item number. The other two columns show the amount we have in stock and the amount being ordered against that stock. The order of rows IS relevant and cannot be changed. Sample output is attached, with the "given" data outlined in a bold border. The only way I have figured out to do this is to nest a whole lot of IF statements in a formula, which is not very elegant or easy to do, and is limited by the number of IF statements that can be nested.
View Replies!
View Related
Canadian Mortgage Calulator  Interest Savings
I'm attempting to build a Mortgage payment calculator which calculates interest semiannually. The goal of the calculator is to determine how much interest you would save if your payments were set to Accelerated Biweekly, and Accerlerated Weekly vs. Monthly. I've already calculated what the payments would be for each payment type, however I'm not able to determine what the interest savings would be.
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 Compound Interest With Capped Earnings
I'm trying to come up with a way of calculating Money earned over time by compounding interest, but with a twist. After reaching a set amount of money, all money above and beyond does not gain interest. example: Principal: $120 (user input value) Duration: 9 (user input value in days, compounding daily) %Intertest: 4% (user selected value, either 2% or 4%) Max interest you can earn: $6 (fixed) Max interest generating money: $150 (variable dependant on %interest, = $150 or $300) Response/Answer is final value. I don't need the daily results like the example. Result would be: $170.84 $124.80 (4.80 interest) $129.79 (4.99) $134.98 (5.19) $140.38 (5.40) $146.00 (5.62) $151.84 (5.84) $157.84 (6.00 reached the max interest level) $163.84 (6.00) $170.84 (6.00) my equations I have so far only do one (below 150 total) or the other (above) but not both. and its just a regular formula: =IF(P<M,IF(P*I^D<M+1,P*I^D,"over limit"),P+6*D)........................
View Replies!
View Related
