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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
Subtract X Amount Every Y Years
I am building an investing strategy model and am looking for a fomula or combination of formulae that would subtract an amount (lets say a 100) every so many years (lets say 10). Data is set up horizontaly, i want to be able to set how much will be subtracted and how often in a "control panel" next to other inputs and variables. I am not proficient in Macros and most often have trouble with them so if its possible to solve this without using any that would be great.
View Replies!
View Related
Calculate Age In Years And Months
I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them. =TRUNC((B1-A1)/365.25)&" Years "& ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months"
View Replies!
View Related
Formula To Calculate Years Months And Days
I am using the following formula to calculate years months and days in Excell 2007 =DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days" C7 3/24/04 D7 1/4/08 What is returned is 3 years, 9 months, 136 days How might if fix it show the correct days?
View Replies!
View Related
Calculate Day, Months & Years Between Dates
I have two columns with dates (and times) in that I am trying to define how many days, hrs and mins have elapsed i.e. A1 has 12/12/06 21:00, B1 has 17/12/06 21:00. C1 has B1-A1 and is custom formatted to show as dd"days" hh"hrs" mm"mins". In this case it will therefore show as 5days 0hrs 0mins. Which is correct. However, if more than 1month has elapsed then the format m"m" d"days" h"hrs" m"mins" does not work. For example 17/03/06 03:00 to 20/12/06 07:00 shows as 10m 4days 4hrs 00min, which it clearly isn't. I know the reason it does this is because it calculates the difference between the two times and adds that to it's 0 value, which in my format is 01/01/1900 00:00. therefore when it adds 277days (the answer) it becomes 04/10/1900 04:00, so my formatting is just calling the month value ('10') and the day value ('4'). I understand the reason it does this, 277 days on from 01/01/1900 is indeed Oct 4th, but 277 days on from 17/03/06 is not 10months and 4 days as there are different length months in between. It also seems to add a month on, possibly because the format for 'months' is between 1 & 12 and therefore cannot begin at 0? Does anyone know if it's possible to force excel to work out the correct number of months and days have elapsed between two dates and not apply it to 01/01/1900? Or any other possible solution, maybe with a different custom format?
View Replies!
View Related
Monthly Depreciation Of An Asset To Automatically Calculate
I am working on a depreciation schedule in which I want the monthly depreciation of an asset to automatically calculate and, if the asset is fully depreciation, caclulate a zero or the balance to be depreciation (if less than the monthly depreciation). Please see below example. As you can see my asset is fully depreciated at the end of February but because there remains a $0.01, the formula is calculating another month in March and then reversing it in April (less the $0.01). Here's the formula I'm using. What am I doing wrong? Column H is March, Column C is my monthly depreciation, and column E is my beginning book value: =-IF(ABS(SUM($F2:H2))>=$E2,(SUM($F2:H2)+$E2),IF($E2=$C2,$C2,$E2))) Purchase PriceMonthly DepreciationAccumulated Depreciation 12/31/20091/1/2010 Beginning Book ValueJan-10Feb-10Mar-10Apr-10May-10 LCD PROJECTOR 797.12 13.29 (770.54) 26.58 (13.29) (13.29) (13.29) 13.28 -
View Replies!
View Related
Calculate Working Shifts Per Day From Monthly Report
I have table and need to take out montly total for each worker... Now... Each hours in day have own factor. (I need total hours per day but for illustration)... So when worker works day shift from 8:00 to 16:00 it's easy... 8 hours When works from 8:00 to 20:00 it's 8 hours + 4 afternoon hours When works from 20:00 to 8:00 it's 2 afternnoon hours + 8 night hours + 2 day hours Aditional problem is when day intercept holliday or sunday when that factors need to be included (if holliday is at sunday then it's like holliday). Here is some attachment: Book1.xls I've also added last day of previous month and first day of next month because of night shifts than need to be calulcated. Therefore correct number of hours is 168 and not 188. Below I calculated manually those numbers wich I want to be automated... Also.. This is table I get.. If it's easier to make it somehow else, OK by me. And any number of aditional columns is not problem...
View Replies!
View Related
Calculate Monthly Standard Deviation From Daily Data
I've daily data of a stock indices returns and I would like to calculate the monthly standard deviation. Currently, I'm using the following worksheet functions: =STDEVP(C2:C20)*SQRT(COUNT(C2:C20)) However, the range changes from month to month, which makes the process of calculating the monthly standard deviation to be quite tedious if I've about 10 years worth of data. I assume I could somehow substitute the range with a dynamic range, but I'm struggling to come up with the correct formulation that would do that.
View Replies!
View Related
Calculate Future Value Of Monthly Recurring, Annually Increasing Payments
follows in paragraph 5 - but first, background! I have a specific formula (received courtesy of some clever person here at Ozgrid (thanks!)) which I use to calculate the Future Value of a series of future payments that increase at a fixed annual rate and earn interest at a fixed rate. Here it is: =Pmt1* SUMPRODUCT((1+Increase_in_payment)^(ROW( OFFSET($A$1,0,0,Term,1))-1),(1+Return_on_investment)^(Term-ROW(OFFSET($A$1,0,0,Term,1))+1)) (Example: $1000 per annum (Pmt1) is invested for 20 years (Term). The interest earned on the $1000 is 10% per annum (Return_on_investment). The $1000 increases by 5% (Increase_in_payment) each year - i.e. 19 increases - answer: $89,632 (rounded)) This formula assumes that the payment is made at the beginning of the period. Question: I would like to change the formula to use MONTHLY payments made in advance, and interest earned on a monthly basis. Because I REALLY do not know what the formula does, maybe I could ask for a detailed explanation thereof - maybe even from the person who supplied it to me (I cannot see who did!) - and then I can start fiddling with it myself if answers do not come. Two previous posts of mine that dealt with somewhat different issues on the same formula are: Determine Present Value From Future Value and [url]
View Replies!
View Related
Calculate Monthly & Annual Returns Of Security Or Index
I feel pretty dumb asking a basic math question but I couldn't find the answer anywhere. I would like to calculate the monthly & annual returns of a security or index. When trying to calculate various indexes to insure my math was correct, my numbers never match that of yahoo or bloomberg's. If you could just start me down the path,
View Replies!
View Related
Calculate Number And Amount Of Payments
The example: Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used. Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00 Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments. So the sort of result I'm looking for would be like Assume todays date is 19-04-09 A B C 12-04-09 £5.00 Last Week 4 Payments Value £45.00 12-04-09 £10.00 13-04-09 £10.00 14-04-09 £20.00
View Replies!
View Related
Formula To Calculate Percent Change, Varied By Amount Of Months
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
View Replies!
View Related
Calculate Amount Of Days Paid In Advance And Apply Percent Discount
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message. give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount? attached is the start of the assesment question. You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid. Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received: • If students pay the course fee less than 7 days prior to the course commencing then they receive no discount. • If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%. • If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%. • If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
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
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
Reference Cell & Add Amount If Positive & Subtract Amount If Negative
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do: I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
View Replies!
View Related
Effective Rate Calculation, Interest Only Loan
I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas? Here is the effective rate calcs on a random normal amortization loan: this is in B2, and answer is 7% =RATE(B4*B5,-((B3+B7)/B6),B7)*12 B3 = Total*Interest 279017.8 B4 = #*Years*in*Loan 30 B5 = #*Payments*/*Year 12 B6 = Total*Payments 360 B7 = Beginning*Principal 200000 B8 - Ending*Balance 0 problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan. Here is the example I'm working on... A client's loan is the following: Loan amount - 131,538 interest rate - 6.15 30 year amortization 10 years interest only normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about $178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan.
View Replies!
View Related
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
Best 3 Of The Last 10 Years
I am working on a retirement calculation sheet. For retirement calculations the employees need to figure out their highest yearly income for three out of their last ten years. The three years can be based on any consecutive twelve months but each of the three years must be the same months, (example, Jan thru Dec, or July thru June). The three years cannot overlap. Here is what I have so far. Column A, paydate. Column B, amount. Column C, year to date amount. Column D, amount for past 12 months. I would like to list the three highest years in the first 3 rows of Column E. Any help is greatly appreciated. Pay Date Gross Year to Date Gross Past 12 Months Highest 3 Years 08/10/06 3,594.58 3,594.58 3,594.58 08/24/06 3,332.79 6,927.37 6,927.37 09/07/06 2,595.59 9,522.96 9,522.96 09/21/06 2,457.36 11,980.32 11,980.32 10/05/06 2,457.36 14,437.68 14,437.68 10/19/06 2,457.36 16,895.04 16,895.04 11/02/06 6,212.51 23,107.55 23,107.55 11/16/06 3,206.09 26,313.64 26,313.64 11/30/06 4,104.56 30,418.20 30,418.20........
View Replies!
View Related
Datedif- Years
I have 2 dates.. 09-feb-09 15-dec-09 Is therea way to calculate the number of years? this is equivalent to 0.27777 years. Is there a way to get this with the datedif or another functiom?
View Replies!
View Related
Printing To Labels For Years
I created a wedding list with a bunch of fields for each household: first name person 1, second name person 1, first name person 2, second name person 2, street #, street address, apartment, city, state and zip. Then I realized that I probably needed to combine fields for each line of the address so I created 3 combined fields: 'combined 1 ' that looks like this "joe smith & patsy cline," a 'combined 2,' that looks like "14 jones street, #3," and a 'combined 3,' that looks like "New York, NY 10037." I haven't printed to labels for years and when I did some quick research via the help function on excel it said to print labels via word...which worked fine until i got to this section: " In the Mail Merge Recipients dialog box, click any column labels in your data that correspond to the Word identifiers on the left. This step makes inserting your data in the form documents easier. For more information about matching fields, see Word Help. " I have no idea what this means, but here's what I need to figure out: " how do I print labels using just 3 of 8 existing columns in my spreadsheet, i.e I just want to use combined 1, combined 2 and combined 3 for the 3 lines of the address. My first instinct was just to cut the contents of the combined fields into a new spreadsheet and just do it taht way, but I created the combined fields using a formula which relates to the other fields........
View Replies!
View Related
Changing Dates To Years
I have list of dates: e.g: D/M/Y 1/03/1997 22/05/2005 13/09/1945 I want a new list that just shows the year and is formatted as a number e.g: 1997 2005 1945 Is there a way of doing this without doing it manually, I have 20,000 observations.
View Replies!
View Related
Advanced Filter Between Years
I have a list of subscribers, each with an account id and the years for which they have subscribed. Each account id can be listed up to five times. I am trying to find out how to use advanced filter(or some other way!) to find those accounts that were subscribers in any of the previous four years but not the current year.
View Replies!
View Related
Dates, Number Of Years
I have two dates, one is a start date the other is todays date, I want to subtract the start date from todays date and show the number of years. But a small twist is I only want to take the years away from each other, ignore day/month. Start 01/05/2000 todays date 01/10/2008 years = 8. Start 10/10/2000 todays date 01/10/2008 years = 7, want it to still show 8 years and ignore the start day/month.
View Replies!
View Related
Concatenating Months And Years
Please refer to the attached screenshot of my working spreadsheet. I'm attempting to concatenate lines 86 and 87, month and year together. The formula I use is: "=C86 & C87" - but I get a large number instead of "October 2009"
View Replies!
View Related
YTD Sum Across Multiple Years
I need to calculate YTD sums using the data that spans three years. I am using SUM(B2:INDEX(B2:AC2,MATCH($B$10,$B$1:$AC$1,0))). This works fine if all of my data is contained in one year. However, when my data spans multiple years it does not. For example, if I have the following and today is April 2007 I want to calculate Jan-April 2007. Labor Type09/0610/0611/0612/0601/0702/0703/0704/0705/0706/0707/0708/0709/0710/0711/0712/0701/08 Bus External2404526907569511,2018808059439237781,1216191,0639378001,156 Bus Internal1,4412,7942,9992,5914,0093,9444,2714,3254,7593,5493,0172,4371,9262,9722,3711,7582,931 IT External1,3483,3993,8653,8586,0247,75510,11110,40911,86911,42311,29710,4878,32713,13412,96912,41917,265 IT Internal3,3537,7428,7107,88412,73112,74614,91114,66215,30413,80513,13511,8248,63511,7728,8876,80911,283 Staff Aug12138195169195408796641626340403428469675604395637
View Replies!
View Related
Calculating Days,Dates And Years
I recently manage to create a spreadsheet. On the spreadsheet what I am looking to do is once I change the year in cell U1 from 2010 to 2011 to automatically change the days and the date number, and where Sat and Sun preferably to auto-fill in yellow the whole column within the table as you can see in the spreadsheet.if not then just do not display Sat/Sun columns at all..
View Replies!
View Related
Counting Number Of Years Between To Dates
I have a list of people with birthdays that needs to be checked against TODAY to determine how old people are. If I subtract the two fields from one another, I get number of days. But is there a way to convert into years? In my attached example, I'd like column C to display 3 (age for person).
View Replies!
View Related
YEARFRAC & Leap Years
It looks like a pretty silly problem but it's driving me mad. The yearfrac function takes a two dates and a basis. Now assume basis = 1 ( Act / Act ) and two cases: 1) 06-Jan-06 -> 06-Jan-08 ( 730 days ) = 1.998175182 =>> divisor = 730/1.998175182 = 365.33333 2) 06-Jan-05 -> 06-Jan-07 ( 730 days ) = 2 =>> divisor = 730/2 = 365 In case 1) 2008 is a leap year ( but the "leap" has yet to come! ). Can anyone explain me the logic behind? I suppose the extra day is divided by 3 but where is the "Actuality" of the divisor?
View Replies!
View Related
Countif- Sheet That Contains Dates Going Back A Few Years
I have a sheet that contains dates going back a few years. I am trying to use the countif function to count the different sites in column B according to date. eg I want to be able to find out how many jobs for PHO1 were created between todays date and 7 days ago, this can go into column C, In column D I need to find out how many jobs were created by PHO1 between 7 and 14 days ago etc etc....
View Replies!
View Related
Adding Years To A Specific Year And Then Rounding It Up
There are two columns in an excel sheet, one is date of birth and other is date of reteirment looks somewhat like this: D_O_B D_O_R 5-Mar-53 31-Mar-11 30-Jun-57 30-Jun-15 20-Jun-51 30-Jun-09 2-Feb-55 28-Feb-13 2-Jul-51 13-Oct-55 1-Sep-51 7-Jul-54 14-Mar-53 3-Aug-50 3 0-Sep-13 Some of the dates in D_O_R are missing. I need to fill in up all the dates in D_O_R column. D_O_R is = 58years+D_O_B(NOTE the dates should come as last date of the month)
View Replies!
View Related
Display Number Of Years Based On A Date
Right now, in a column I would like to display an number (length of employment) based on the hire date. In one cell the employee's hire date is entered. In a column of other cells the pay period ending date is displayed and in another column the length of employment is displayed. Example: 03/17/2000 03/01/2009 8 03/15/2009 8 03/29/2009 9 04/12/2009 9 04/26/2009 9 How would I create a formula in the length of employment cells that would indicate the correct number of years based on the hire date and adjusts when the pay period date passes this hire date?
View Replies!
View Related
|
TRACKER
