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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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
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
Payment Staus Sheet
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 Replies!
View Related
Night Payment Calculator
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 timesheets. 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 Replies!
View Related
Entering A Payment And Seeing A New Balance
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 Replies!
View Related
Conditional Payment Terms
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 Replies!
View Related
Adding A Payment Based On 2 Values
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 Replies!
View Related
Payment Expense Spreadsheet With Gst
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 Replies!
View Related
Debt Reduction By One Time Payment In Amortization?
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 OneTime Payment added with the EMI for the 13 the mOnth to reduce the Principal and accordingly the tenure?
View Replies!
View Related
Payment Function Giving Negative $ Result
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 Replies!
View Related
Payment Days Table (change The Range For Each Time)
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 Replies!
View Related
Vexxing Lookup: Update That Supplemental Payment Based Upon Certain Conditions
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 Replies!
View Related
Bill Payment Calendar Should All Be Automatically Calculated Under The “Date” Column
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 nonworking 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 nonworking 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 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
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
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
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
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
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 1014 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
Interest Over 'X' Months
I'm trying to find a formula that will give me the following: Rent is $2.00 a month with a 3% escalation each year. What is my Rent after 5 years? Now the other thing is that I need the number of years to be a formula that counts the number of columns with data up to the column with $0.00.
View Replies!
View Related
Formula For Deleting A Formula When The Formula Has Done Its Calculation
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating. I've got a formula =IF(COUNTA(A1)=1,TODAY(),"") When something is typed in Cell A1 the cell with the formula will input todays date. Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.
View Replies!
View Related
Combine Formula Which Reduce The No. Of Additoanl Formula's Columns
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass. To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them. Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
View Replies!
View Related
Add Addition If Condition To Existing Formula: Long Formula
This task joins a string together based on a number of characters per cell in the range. I want to isolate one range, Col N, and add an IF condition to it. There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
View Replies!
View Related
Date Formula & Add If Statement To The Existing Formula
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
View Replies!
View Related
Replace Character Used In Formula Where Formula References Cell
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url. i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6 this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
View Replies!
View Related
