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.
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 2 Replies View RelatedI can't find a custom format to validate a properly formatted Canadian postal code on entry, so I think my last hope is to ask someone to write me a VBA code.
Canadian postal codes consist of six characters with a space in the middle: a capital letter, a number, a capital letter, a space, a number, a capital letter, and a number.
Therefore, M2A 3J4 is a properly formated postal code.
I want a code to fix an improperly fomatted postal code (such as M2A3J4 or m2a3j4 or m3a 2j4) on entry.
I have a problem calculating a total interest for an input period that has different interest rates. You can see the attached file: INTEREST.xls
I have used a function but the result comes out wrong. Can I fine tune it or do I have to change my method of calculating.
I set up a formula knowing only two variables?
1. Assuming I know how much per month one can pay in total, and
2. The percent interest rate
... I need the total amount that can be financed.
Example:
A. $3,900 maximum per month
B. 5.00% interest rate
C. = x (the amount to be financed)
Scenario - I started doodling with buying a property to let and then started thinking about adding the "shortfall" between the required bond payment and the income generated by the property back onto the loan each month as the shortfall arises. Let's assume that the financial institution will "pay" this shortfall from a previously agreed upon additional amount available on the property - understand?
While there is a shortfall between the income generated by the property and the bond payment, insurance, taxes etc., the outstanding capital will obviously increase as each new month arises and each specific shortfall is added onto the outstanding capital (loan at the bank).
Once the shortfall turns into surplusses, the surplusses are in turn paid into the account until the bond is paid in full.
With a normal, say 15 year, bond, the interest payments and principal payments of the constant mortgage payment (assume fixed interest rate) nicely works out to a balance of 0 in month 180, but not in the scenario I describe.
What happens in the scenario I described above is that, if I keep the payment constant (=PMT(and the rest - calculated on the original term and the original investment) each month's deficit is added to the outstanding capital but the payment stays constant. I want this payment to remain constant throughout the bond.........
I'm trying to make an amortization table with all the individual payments. I'm trying to avoid putting in the formula manually into each individual cell. I have a formula = ROUND(F11*0.12/12,0) and I want to insert that formula down an entire column, but increasing the F11 to F12, F13, etc.
View 4 Replies View RelatedHow do I come up with a price that will make my profit and my customers savings equal.
Example:
Retail price $178.99
My cost $113.46
If I charge $142.23 then my profit is $28.76
Customer pays $142.23 plus $8.00 shipping ($150.23)
$178.99 Retail minus $150.23 = $28.76 savings
I need to calculate the final balance of a savings account. Thought this could be done using the function FV. However I had not used this function before. Therefore to make sure that I had set up the formula correctly I checked it manually with a small example. The results are set out below. You will see the function FV fails to include the final interest payment. (£2620.32 - £2472.00 =£148.32)
The data to be used will be:
A regular payment of an identical amount on first day of every year.
Compound interest calculated annually and credited to account on final day of every year.
Period of years.
Example of Saving Regular Amount every year at Compound Interest
Using =FV(A1,A2,A3)
Amount of Saving per annum £1,200.00 A1
Rate per Annum 6.00% A2
Period of Years 2.00 A3
£2,472.00
Manual calculation of example:
Payment at start of first year£1200.00
Interest at end of first year 72.00
Balance at end of first year 1272.00
Payment at start of second year £1200.00
Balance at start of second year 2472.00
Interest at end of second year 148.32
Balance at end of second year £2620.32
I want to save one million dollars in 10 years earning 8.00% interest. How do I calculate how much I need to save per month.
View 11 Replies View RelatedI have found an older thread that explains how to determine if a date falls in DST or not. See here Daylight Savings Time Funcitions. It does not follow the DST rules we use here in Canada and the formula is very confusing to me as it uses arrays, something I have yet to fully understand. DST in Canada begins the 2nd Sunday of March and ends on the 1st Sunday in November. I have a date in one column and a time in the next. What I would like to do is have a formula that checks to see if the date falls in DST and if so adds 1 hour to the second column. The result will show up in a third column.
View 4 Replies View Relatedhow many debits can take place in my Savings Account if I provide the Current Balance and the No of Debits,Amount of Debits,Frequency of Each Debit.
Lets say, I have Rs 42978/- in my savings account at this moment and I have 2 different Debits taking place on different dates of the Month.
First Debit of Rs 750/- (12th of Month)
Second Debit of Rs 584/- ( 27th of Month)
I am also planning to add one more debit EMI (Equated Onthly Installments)for Rs 1127/- every month.
I need to know the No of Months I can go without paying my Savings Account as well as the Month and the year.
I have tried doing it the regular way but it becomes quite cumbersome, I am looking for help in terms of a better design or a Template as some single-cell (hopefully) formula which can incorporate the number of Debits,Amounts etc.
One very important thing is to also keep a track of the Balance not going below an "X" amount and that is Rs 1500/- as thats the Bank's Minimum Balance requirement..
The no of Installments are as mentioned below:
Debit--- Amount--- Start Month--- No of Installments
I Debit--- 750--- Jan-09--- 36
II Debit--- 584--- Feb-09--- 27
III Debit--- 1127--- Mar-09--- 60
I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.
View 3 Replies View RelatedWriting code to do the following:
Save advanced filter settings
Remove filter (or simply set to be 'select all'
Run other code (I have this piece of course)
Put filter back on with same selections chosen as when it was removed
(Need this becuase the code in the middle does not work properly when the data is filtered)
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
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.
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"))
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.
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.
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.
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?
How can I find the total of interest (or and expense) during a given calendar range
Data is as follows
Date Type Amount
4/1/11 Interest 4.00
4/15/11 charge 22.00
5/1/11 interest 3.00
I want a cell to compute the interest in april only.
I have tried =sumif(range, end date, amount_range)- sumif(range, beg date, amount_range)
but how do I nest the type argument into the argument
Possible approaches advanced filter? and function? add another column that uses an if statement to do interest only and do the sumif statement above I was hoping to have a drop down box that could have interest, COGS, beside it and april, may, june above it
Is there a function that calculates what interest rate is needed to turn a $2000 investment into $9000000 in 30 years based on compounding interest?
View 9 Replies View RelatedI 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.
does anyone know how to calculate the interest so it matches this? .......
View 13 Replies View RelatedSample 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.
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?
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?", _ ......................................
I am trying to create a calculator based on a worksheet I have. I do not know how to write a formula for simple interest calculations and for compound interest calculations.
View 4 Replies View RelatedI 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.