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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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/P-1) 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
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
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
Compound Rate: Annual Growth Rate %
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight. r = 100((Y/X)^(1/n))-1) So for X = 200, Y = 750, n = 10, we have r = 100((750/200)^(0.1))-1) = 14.1309%
View Replies!
View Related
Effective Date For New Hires
I have Windows XP Professional 2002 I have not worked with dates other than calculating ages. Simple things. My lastest formula I need is as follows: Effective Date for Benefits to Start for New Hires Date of Hire examples: either the first of a month -- 06/01/07 OR 06/??/07; example any date of the month other than the first Benefits are effective the first of the month following 90 day waiting period. Example: 06/01/07 = 09/01/07 06/05/07 = 10/01/07
View Replies!
View Related
Calculate Effective Time
I have a sheet for tracking my poker playing online. In column A I enter the start time as an Excel Dates & Times and in column B I enter the end time in the same format. It could look something like this: Start time End time 2007-01-03 18:20 2007-01-03 18:50 2007-01-03 18:30 2007-01-03 18:45 (Swedish date formatting) This means I have been playing two tables simultaneously. Now I could get total time in "table hours" using SUM Formula: =SUM(B:B)-SUM(A:A) which in this case would result in 45 minutes. But I also would like to calculate effective playing time so if I play 10 minutes on two tables simultaneously it will only count as 10 minutes and not 20 minutes.
View Replies!
View Related
Finding Effective Solution For Updating Links
I've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???
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
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
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
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
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 10-11 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 5-10-15-and 20 years.
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 dont 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
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
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 Replies!
View Related
Calculate Interest Per Month
Sample data: 02-06-09 to 18-06-09 earned $2500 19-06-09 to 23-08-09 earned $3500 24-08-09 to 31-12-09 earned $4500 I am thinking if there's a formula that can calculate the interest earned for each month from Jun-09 to Dec-09.
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) Course-rows which require action/follow-up are highlighted with a certain color. The "completed" course-rows 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 pop-up box of some sort which would display the "highlighted" rows (maybe from A through I or something). I could add 'follow-up' flags on another column if the highlight color won't do.
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
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
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
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
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 (720-739, 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 720-739. 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 add-ons, 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 2-unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 >740 3-4 unit 1 1 1 1 n/a n/a n/a 720-739 all -0.25 0 0 0 0 0 0 720-739 Cash Out 0 0.125 0.125 0.375 0.5 0.5 n/a 720-739 Investment 1.5 1.5 1.5 2 2.25 2.5 n/a 720-739 2-unit 0.5 0.5 0.5 0.5 0.5 0.5 0.5 720-739 3-4 unit 1 1 1 1 n/a n/a n/a
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
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
Canadian Mortgage Calulator - Interest Savings
I'm attempting to build a Mortgage payment calculator which calculates interest semi-annually. The goal of the calculator is to determine how much interest you would save if your payments were set to Accelerated Bi-weekly, 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
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
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
Compound Interest After Mortgage Free Point
I've found this calc but it doesn't compare to my particular scenario/goals: http://dinkytown.net/java/InvestmentDebt.html. The major difference is that this dinkytown calc requires a new loan to be put in place and I am not in a position to refinance my current mortgage. My plan is to pay an extra $267 (b3) to the mtg (b4). It will take 128 months (b6) to pay off the mortgage. After that point, I need to calculate what the newly freed capital (b5) would do if I put in an account (i.e. a CD or a simple US savings account) that had an Annual Percentage Yield (APY) of 1, 2, and 3% for 20, 15, 10 and 5 years. I thought that I had the right formula in place for cells b7:b8 should the account earn a 0% rate of return, but I think it's faulty since it gives me a negative number for 10 and 5 year accumulation periods.
View Replies!
View Related
Last Purchased Rate
A B C D 1 Date code price 2 10/09/08 abc101 1100.00 3 12/10/09 abc104 1255.00 4 30/08/09 abc101 1389.00 In D1 I want the last purchased rate of a code ( suppose here it is abc101, but it would be different for other item). Here Date is dd-mm-yy.
View Replies!
View Related
RATE FORMULA
I would like to know the formula used in excel for rate function. Anybody knows and can share with me ? as i need to use that in other programmining language where these financial functions are not available.
View Replies!
View Related
Look Up A Rate Based On Another Value
I am trying to apply a multiplication factor that would vary depending on the sum of a particular cell. For example let's say the value of cell A5 is the SUM of A1:A4. Let's say the value is 255,000,000. my multiplication factor table looks like this: 1 - 50,000,000.........................(0.17)/1000 50,000,001 100,000,000..........(0.13)/1000 100,000,001 250,000,000.........(0.10)/1000 250,000,001 500,000,000.........(0.08)/1000 500,000,001 750,000,000.........(0.07)/1000 750,000,000 ++ .......................(0.05)/1000 So depending on the range the cell sum falls into I need the formula to apply the appropriate multiplication factor and divide by 1000. I'm trying to avoid manually inputting the (0.XX)/1000 based on the formula identifying which range the sum falls into and automatically applying the appropriate multiplication factor. In the example above the formula would be applied to the A6 cell and identify the A5 sum as falling into the 250,000,001 - 500,000,000 "range" and automatically apply the corresponding multiplication factor.
View Replies!
View Related
Rate Function
I have pv, fv, and period. there will be no payments made. I want the interest rate. I do it long hand it works. I do it using the rate function it doesn't! This also doesn't work with nper. Here is a simplified excel example. What am I doing wrong?
View Replies!
View Related
Update Specific Cells Within A Column Used For Compound Interest
I have a column of 30 cells, each showing retirement dollars earned based on years of service (cell 1 = year 1, etc) and salary. Here is the predicament: A new hire is brought in with 10 years service, stays 5 and then quits. How do I change only cells 10-14 reflecting his/her salary and retirement dollars? Remember, cell 10 is the first year for the compound interest, cell 11 the second year, etc. I currently use the following formula for cell 10 (compounded salary from cell 9 * (1+.05)^1). Cell 11 is (compounded salary from cell 10 * (1.05)^1.
View Replies!
View Related
Populate Rate Table
In Table A, I have the rate table for an insurance product. I need to build a formula that will outomatically populate the data in Table B based on plan, start age and deductibles. Example, if a person selects age 32 years, deductibles as 0 and plan 4, then his yearly premium will be $750/12=$62.50.This will automatically increase when his age falls into the next age bracket which is 36-45 years and his premium at age 36 to 45 will be $970/12=$80.83. The Table B should populate all this information from the start age to age 80 when the policy stops. An example of table B is shown in the attachement for someone who starts at age 32, chooses deductibles as 0 and plan 4.
View Replies!
View Related
|
TRACKER
