FV Function. Final Balance Of Savings

Mar 28, 2008

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

View 5 Replies


ADVERTISEMENT

To Get The Exact Count Of Debits And Also The Date Of The Balance In The Savings Acct

Jan 18, 2009

how 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

View 14 Replies View Related

Apply Credits To Running Balance, Pick Smallest Balance

Oct 26, 2006

For the small database in my example workbook, I would like to apply credits earned at a later date to the oldest charges and create a "To Date" balance. After creating the "To Date" balance, I would like to select the smallest "To Date" balance as a way to identify the first charge that has no payment. Please see the attached for more clarity and additional information. The last column shows the desired results.

View 9 Replies View Related

ARRAY.JOIN Function And Eliminate Any Blank Cells From The Final Column

Jan 30, 2009

I am trying to use the ARRAY.JOIN function as recommended by Domenic in previous posts. I need to output a single column of values from a 10 x 10 grid that does not have values in every cell.

(I would also like to eliminate any blank cells from the final column.)

I can only get ARRAY.JOIN to output the first value of whatever range I select, not a column of values.

View 3 Replies View Related

ARRAY.JOIN Function: Eliminate Any Blank Cells From The Final Column

Feb 2, 2009

I am trying to use the ARRAY.JOIN function as recommended by Domenic in previous posts. I need to output a single column of all the values from a 10 x 10 array that does not have values in every cell. (I would also like to eliminate any blank cells from the final column.) I can only get ARRAY.JOIN to output the first value of whatever range I select, not a column of values.

how to properly execute that function. One earlier comment was that I was not specifying criteria. I'm not sure from the description and help section of "morefunc" as to what those criteria specs are, other than specifying the whole array. I am attaching a test file with the 10x10 grid & some missing values in A1:J10. Cell L1 has the ARRAY.JOIN function with the entire grid specified, but the output is only the first cell of the grid.

View 2 Replies View Related

Create Function To Balance Items That Are Subordinate

Jan 29, 2007

I am trying to enhance our estimating spreadsheet. If you look at the example attached it is a snippet of the spreadsheet. What I am trying to do is make column AJ (balanced bid unit) include subordinate items. The way a sub item will be defined is column B (bid item) if column B = S, then its balanced bid unit need to be reflected in the column AJ. I would also any item titled as Y in column B (bid item) to include all listings as S (until next item as Y) in the column AJ totals.

View 9 Replies View Related

Account Balance Sheet, Balance At The End

Jul 31, 2006

need a formula that will give me a balance at the end, needs 2 columns with charges and credits and it allways give correct balance total on last column. have not used Excel for long time and forgot.

View 5 Replies View Related

How To Make Profit And Savings Equal

Dec 18, 2012

How 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

View 9 Replies View Related

Calculate Savings Required To Reach Goal

May 21, 2009

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 Related

How To Determine If Date Is Daylight Savings Time

Aug 1, 2014

I 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 Related

Canadian Mortgage Calulator - Interest Savings

Mar 29, 2009

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 9 Replies View Related

Cut And Move From Row 2 To Final Row?

Jun 14, 2012

I need to cut and move data in a worksheet where the bottom always changes. The header row should stay in worksheet 1 and row 2 down to the bottom needs to be cut and moved to worksheet 2. One time it could be 100 rows and the next time a thousand or more.

View 5 Replies View Related

Final Ranking

Feb 12, 2009

I need to do a final ranking for the employees those who have been already ranked on three different segments. And there will be also employees who are not qualified for the ranking in any one of the three different segments. So in the final ranking need to find the final qualified employees who are ranked in all the segments and we need to rank them, and the employees who are not qualified also has to be mentioned.

View 9 Replies View Related

Create A Spreadsheet That Will Calculate Total Money Spent And Total Savings?

Mar 5, 2014

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 Related

Marked As Final, Didn't Mean To.

Jan 31, 2010

I think the title pretty much says it all... Now I have a file I can't send to anyone to do anything with... I've googled a few things but I've found nothing to work with... It was only a few hours worth of work but this seems ridiculous...

View 3 Replies View Related

How To Get Final Figures From Raw Data

Feb 13, 2014

DATE
PRODUCT
BALANCE

1/2
KEY CHAIN
4

1/2
KEY CHAIN
8

1/2
KEY CHAIN
27

[code]....

How to get final figure each product from sample data above:

the result:

Key chain = 27
Gift = 4
Hamper = 1

View 7 Replies View Related

Sheets With Different Final Row Variables

Mar 26, 2009

Finalrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Finalrow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

I need my "Do until" loop to run until the activecell in sheet1 =finalrow

I tried "Do until activecell.row=Finalrow", however that didn't work because it ended my macro too soon.

I tried "Do until sheets("Sheet1").Activecell.row=Finalrow
But it won't accept that.

View 9 Replies View Related

Save Filter Savings To Run Macro And Then Put Filter Back On

May 25, 2012

Writing 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)

View 1 Replies View Related

Formula To Pull The Final Value In A Column

Jun 15, 2009

If I have a columns of numbers that vary in length, and can have empty cells between the final value and the formula to pull the number (of varing lengths), what formula can I use to repeat the final value?

For example - here are two columns:

4,800,000
0
46,594
space
space
<formula to pull 46,594>

or

56,000
48,150
36,528..............

View 2 Replies View Related

For Next Loop Not Stopping After Final Item

Jan 21, 2012

I've modified some code to perform a loop which creates a new worksheet for each item in a pivot table Page By field (EmpName), copying and pasting values for the displayed pivot table with each name selected. Everything is working fine until it gets done with the last item in the list, at which point I get a Run Time Error 1004, "Unable to Get Properties of the Worksheet Class".

I want the MsgBox to pop up at the end, stating how many sheets have been added.

Sub Loop_PivotItems()
'Turn off screen updating
Application.ScreenUpdating = False
'Store the sheet with the Pivot Table
Piv_Sht = ActiveSheet.Name
'Loop through every PivotItem in the PageField (Filter) of the Pivot Table

[Code] ...........

View 2 Replies View Related

Summing Final Two Values In A List

Aug 15, 2012

I am attempting to sum the final two values in a list. Each week, I add a new value to the bottom of the list. I would like the formula to calculate only the final two values, and update each week. For example, in Week 1, I would have the values 4,10,12,6 in Cells A1, B1, C1 and D1. The formula would calculate the sum of C1 and D1 = 18.

In Week 2, I would input the value of 15 into Cell E1. I would want the formula to now calculate the sum of D1 and E1 = 18.

View 9 Replies View Related

Getting The Final Sheet And Cell Address Of A Value

Mar 24, 2007

If coded correctly, there are various Excel functions & tricks for creating a formula to send someone on a wild goose chase through many worksheets and cells before finding the final cell that contains the final value. I was able to use =Cell("address",...) function to cut through the many paths and retrieve the address of the final cell. Is there a VBA trick for doing this to the active cell? I would like to be able to run a small VBA routine that would trace back the fomula of the active cell and return the final cell's worksheet and cell address.

View 9 Replies View Related

Fill Down Based On Another Lists Final Value

Jul 13, 2007

I run a report of varying length that generates times. I need to take the final time from 'Column J' and make that the final value for 'Column U'. 'Column J' has a varying number of entries and the times are not equally spaced out. In 'Column U' I want to go from the start time (which I have referenced from J:2) to the final value in 'Column J' and include every minute in between. Then I need to take the new list of every minute from 'Column U' and make it the x-axis of a bar chart.

View 2 Replies View Related

Loop Copy And Paste Until Final Sheet?

Jan 16, 2014

I'm currently running this macro:

VB:
Sub CopyNextTab()
WorkbookName = ActiveWorkbook.Name
Cells.Select

[Code]....

I'm not entirely sure if this is correct, but what I'm looking to do is the following:

1. On the currently selected sheet, copy all data into the "Pasted Data" sheet on "Test.xlsm"
2. On the "Calculations sheet", copy all information across to the next available row on "Master Sheet"
3. Select the original Workbook and move to the next tab
4. Repeat until there are no more tabs remaining

So far as I can tell steps 1-3 are working (however I'm not entirely confident with my code to move to the next sheet as I'm not sure it will end the sub on the last sheet).

View 5 Replies View Related

Multiply Column - Final Formula For This Spreadsheet?

Jul 30, 2014

I am having trouble with the final formula for this spreadsheet.

I want to be able to select "Holiday/Sunday" from the drop down men in column H, and have it multiply the total in column F by 2. There is already a formula in column F that sums column E-D.

View 4 Replies View Related

A Text Number With Exactly 6 Digits In The Final Outcome

Mar 4, 2009

I am wanting to create a VB script that will take a number (in general format) of two or three digits, convert it to text, then make sure that there are enough zeros preceeding the number to make exactly 6 digits. Examples of the initial number (before the macro is run) and the final number (after it is run): ..

View 8 Replies View Related

Check If Final Week Of Fiscal Period

Oct 8, 2008

I am attempting to check to see if the current week is the end of a period or not. If it is, I need to display some text, and if it is not, I need to display nothing.

Since I am Computer Science major, the best way I can describe this is through and if structure from a low-level programming lanuage (like C++ or Java):

View 2 Replies View Related

Calculate Final Grades With Weighted Averages?

Mar 19, 2013

I am trying to calculate final grades with weighted averages. However for the final grade I need to drop the lowest of the test grade which come after the bold line to the right, making the total relative weight of Tests, the weight of 2 tests. How would I go about calculating that with a SumProduct formula? I do not need to drop grades from B4:K4, however from L4:N4 I need to drop the lowest grade for my final grade, which needs to be rounded to the nearest integer. The relative weight of each assignment is given in Row 1.

View 3 Replies View Related

Saving Final Date With Logic Statement

Apr 28, 2014

I have a workbook that is titled "Results". It has 2 columns that I need, a 'status' column (B) and a 'finalized date' column (C)

Column B is the 'status' column: Formula: ('[May Database]Summary'!$B$1)

The May Database B1 column is a dropdown with options "In Progress" and "Final" that will be switched manually.

The results workbook pulls it's status from the May Database workbook.

So I need my results workbook to update it's 'finalized date' column (C) when the May database says "Final" but populate and save the day that the May database was manually switched to 'Final'

My question is would this formula work?:

Results workbook status column: C1= IF(B1="Final", Now(), "")

Would this save the date when the May Database was manually switched to 'Final' or will this just update my current day everytime I open this workbook since Now() returns current day? Is there a way to save a date when it was finalized and then keep that date?

View 1 Replies View Related

Move Final Word In Cell To Next Column?

Jul 2, 2014

So I have a few hundred spreadsheets with the following structure:

Persson L. S skogsarb Granberg Malung
Persson M R fru
Persson N skogv Sjog 23 Torsby

What I want to do is move/split the ultimate word in every cell in column A to a parallel cell in column B, and then delete the word from column A.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved