Need Calculations for Investment Portfolio
Kindly request help in formulating calculations for investment portfolio. I have two tables: EQUITIES and TRANSACTIONS.
The EQUITIES table is a database of all equities including the following fields: _UniqueID (auto enter ser.#); Equity (text); Quantity, sum(TRANSACTIONS::Units traded); Market Pice (number); Market Value (Quantity x Market Price); Total Cost Basis, Sum(TRANSACTIONS::Cost Basis); Unrealized Gain (Loss), (Market Value - Total Cost Basis); and Holding Days.
The TRANSACTIONS table is a database of all transactions including the following fields: _UniqueID (auto enter ser. #); _EquityID(auto enter ser. #); Equity (text); Units Traded (number); Pur. Price (number); Commission (number); Trade Date; Cost Basis (Units Traded x Price) + Commission; and Cost Per Share (Cost Basis/Units Purchased).
Kindly request calculation and instructions in setting up a table that records sales. For example, if you only sell a part of an equity, IRS rules suggest three ways to account for capital gains/losses: First in first out (FIFO) of all Units Trades; Last in first out (LIFO); or average cost of all Units Traded.
There are three issues in developing these calculations: First issue concern the length of time for holding the security. This is represented in the last field of the EQUITIES table. What is the calculation to determine holding days (TRANSACTIONS::Trade Date to present) to determine whether this is a short-term or long-term held asset having tax implications, and how does one set up the conditional formatting if the period < 365 is short-term, and period => 365 is long-term asset; Second issue is to allow trader the opportunity to determine the cost basis of the sell transaction: FIFO; LIFO; or average cost of Units Traded. Kindly help me formulate these calculations. Also, is an EQUITIES SOLD Table needed and how would it be set up? or can one use the TRANSACTIONS Table for sales as well as purchases?